*
; options nosource; /*written by Arnold Schick, University of Marburg/Germany using SAS 6.10 under MS Windows 3.11 tested in SAS 6.09 on AIX Unix April 29, 1996 corrected: May 20, 1996 (column geometric mean) Note: If you want rows and columns geometric mean, the macro GMEAN should be applied in the order: 1th: ROW as ASPECT parameter 2nd: COL as ASPECT parameter only positive numerics are used from input data set, a negative numeric value results to missing, characters are excluded, however allowed. formula by Dr John Whittington, UK */ %macro geomean (data, aspect, result); options nomprint nonotes nosymbolgen; %if %upcase(&data) = HELP %then %do; %put; %put note: input data set name was &data, sorry, giving help information:; %put; %put This macro GEOMEAN computes the geometric mean; %put from columns or rows in a SAS data set with values > 0; %put; %put Macro Parameter Specification: ; %put; %put DATA = input data set name, default is HELP; %put ASPECT = specifies ROW or COLUMN for the computed mean, default is ROW; %put RESULT = output data set name, default is _NEW_; %put; %goto next; %end; %if &result = %then %let result = _NEW_; data _NULL_; if 0 then set &data nobs=n; call symput ('n',n); stop; run; %if &n=0 %then %goto quit; %if %upcase(&aspect)=COL or %upcase(&aspect)=COLUMN or %upcase(&aspect)=COLUMMS %then %let aspect=column; %else %let aspect=row; %if &aspect=column %then %do; proc transpose data=&data out=_temp_; run; %end; %else %do; proc transpose data=&data out=_temp_; run; data _ident_; set _temp_ end=last nobs=n; length _name_ $8.; keep _name_ number; number = _N_; if last then do; call symput ('n',n); output; _name_ = 'GMEAN'; number +1; end; output; run; data _temp_; set _temp_; drop _name_; run; proc transpose data=_temp_ out=_temp_; run; %end; %let n=&n; data &result; *code by Dr John Whittington, E-mail: johnw@mag-net.co.uk; set _temp_ end=last; length gmean 8 prod 8 ; drop i prod; array x(*) col1 - col&n ; prod = 1 ; do i = 1 to dim(x) ; if x(i) ne . then prod = prod * x(i) ; end ; if prod < 0 then put 'macro-warning: negative value at column ' "&n"; else gmean = prod ** ( 1 / &n); run; %if &aspect=column %then %do; proc transpose data=&result out=&result; run; options notes; data &result; set &result; if _name_ ^= 'GMEAN' then _name_ = 'ROW' || substr(_NAME_,4,4); run; options nonotes; proc datasets nolist; delete _temp_ ; quit; %end; %else %do; data &result; set &result; drop _name_; run; proc transpose data=&result out=&result; run; data &result; set &result; drop _name_; number = _N_; run; data &result; update &result _ident_; by number; run; proc transpose data=&result out=&result; run; options notes; data &result; set &result end=last; drop _name_ ; if last then delete; run; options nonotes; proc datasets nolist; delete _temp_ _ident_; quit; %end; %goto next; options notes; %quit : %put data set &data is empty; %next : ; options notes; %mend; options source; *Example; *to run, please remove comment brackets; /* %geomean(help); data eins; length mm $2.; do a1=1 to 10; a2=a1*a1; a3=a1/a2; mm='jj'; a5=a1+a3; b7=a1-a3; umsatz=a1+a5-b7; /* tests negative values: nnn=umsatz-a2; */ output; end; run; %geomean(eins, row, res1); %geomean(res1, col, res2); proc print data=res1; run; proc print data=res2; run; */ *;