|
Version 1.3 (June 2013)
SAS macros %AnyOf
SAS macros %MeanOf
SAS macros %NOf
SAS macros %SumOf
Calculating the mean or the sum of several variables in proc sql
Syntax
%AnyOf(vars=, calculated=, cond=eq 1)
%MeanOf(vars=, calculated=) %NOf(vars=, calculated=) %SumOf(vars=, calculated=) %AnyOf, %MeanOf, %NOf and %SumOf arguments list
Code
%macro AnyOf(vars=, calculated=, cond=eq 1);
%local mc mv n v; %local allvars var; %if %length(%superq(vars)) eq 0 %then %let mv = 0; %else %let mv = %ntokens(&vars); %if %length(%superq(calculated)) eq 0 %then %let mc = 0; %else %let mc = %ntokens(&calculated); %let allvars = &vars &calculated; %let n = %eval(&mv + &mc); %do v = 1 %to &n; %let var = %scan(&allvars, &v); %if &v gt 1 %then %do; or %end; %if &v gt &mv %then %do; calculated %end; &var &cond %end; %mend AnyOf; %macro MeanOf(vars=, calculated=); %local dim i v mv mc n; %local calculated allvars; %if %length(%superq(vars)) eq 0 %then %let mv = 0; %else %let mv = %ntokens(&vars); %if %length(%superq(calculated)) eq 0 %then %let mc = 0; %else %let mc = %ntokens(&calculated); %if &mv eq 0 and &mc eq 0 %then %do; . %end; %else %do; %let allvars = &vars &calculated; %let n = %eval(&mv + &mc); ifn( %do dim = 0 %to 2; /*dim = 0 for condition, 1 for numerator, 2 for denominator */ %let calculated = 0; %do i = 1 %to &n; %if &i gt &mv %then %let calculated = 1; %let v = %scan(&allvars, &i); %if &calculated %then %let v = calculated &v; %if &i gt 1 %then %do; %if &dim eq 0 %then %do; or %end; %else %do; + %end; %end; %if &dim eq 0 %then %do; not missing(&v) %end; %else %if &dim eq 1 %then %do; coalesce(&v, 0) %end; %else %do; (not missing(&v)) %end; %end; %if &dim eq 0 %then %do; , ( %end; %else %if &dim eq 1 %then %do; ) / ( %end; %else %do; ) %end; %end; , .) %end; %mend MeanOf; %macro NOf(vars=, calculated=); %local i v mv mc n; %local calculated allvars; %if %length(%superq(vars)) eq 0 %then %let mv = 0; %else %let mv = %ntokens(&vars); %if %length(%superq(calculated)) eq 0 %then %let mc = 0; %else %let mc = %ntokens(&calculated); %if &mv eq 0 and &mc eq 0 %then %do; . %end; %else %do; %let allvars = &vars &calculated; %let n = %eval(&mv + &mc); %let calculated = 0; %do i = 1 %to &n; %if &i gt &mv %then %let calculated = 1; %let v = %scan(&allvars, &i); %if &calculated %then %let v = calculated &v; %if &i gt 1 %then %do; + %end; (not missing(&v)) %end; %end; %mend NOf; %macro SumOf(vars=, calculated=); %local dim i v mv mc n; %local calculated allvars; %if %length(%superq(vars)) eq 0 %then %let mv = 0; %else %let mv = %ntokens(&vars); %if %length(%superq(calculated)) eq 0 %then %let mc = 0; %else %let mc = %ntokens(&calculated); %if &mv eq 0 and &mc eq 0 %then %do; . %end; %else %do; %let allvars = &vars &calculated; %let n = %eval(&mv + &mc); ifn( %do dim = 0 %to 1; /*dim = 0 for condition, 1 for numerator */ %let calculated = 0; %do i = 1 %to &n; %if &i gt &mv %then %let calculated = 1; %let v = %scan(&allvars, &i); %if &calculated %then %let v = calculated &v; %if &i gt 1 %then %do; %if &dim eq 0 %then %do; or %end; %else %do; + %end; %end; %if &dim eq 0 %then %do; not missing(&v) %end; %else %do; coalesce(&v, 0) %end; %end; %if &dim eq 0 %then %do; , ( %end; %else %do; ) %end; %end; , .) %end; %mend SumOf; %macro ntokens(list); %eval(1 + %length(%sysfunc(compbl(&list))) - %length(%sysfunc(compress(&list)))) %mend ntokens; Example
proc sql;
create table NewTable as select idNumber, x, y, abs(x) as absoluteX, %MeanOf(variables=u v) as uvMean, %MeanOf(variables=a, calculated=absoluteX) as aAbsXMean, %NOf(vars=Q1Score Q2Score Q3Score Q4Score Q5Score) as nAnsweredQuestions, 10*Q3Score as Q3Score, 5*Q5Score as Q5Score, %SumOf(variables=Q1Score Q2Score Q4Score, calculated=Q3Score Q5Score) as TotalScore, %AnyOf(vars=Q1Score Q2Score Q3Score Q4Score Q5Score, cond=gt 10) as AnyScoreGreaterThan10, from SourceData; quit; |