topic Re: Proc Summary - Dynamic aggregates vars in Statistical Procedures

Proc Summary - Dynamic aggregates vars

aj34321 — Wed, 01 Apr 2015 05:48:13 GMT

D/All,

I have this proc summary to which i'm passing dynamic variables for classes and aggregations using a macro variable.

The challenge is i will not know which all class and aggregates variables are passed but i want below proc summary to be created. During execution the code should iterate thru number of aggregate vars, build and append to proc summary. I may pass just one aggregate var or a max of 10/12 vars.

%let var_classes = class1 class2 class3 class4 class5

%let var_aggregates = var1 var2 var3 var4;

proc summary data = in_data. noprint nonobs;

var &var_aggregates.;

class &var_classes.;

output out = imp1_out_data.

sum(var1)=

sum(var2)=

sum(var3)=

sum(var4)=

/autoname levels ways;

run;

Please note the variable names are not var1 var2 var3 var4 but will be actual names from the dataset. Only for illustration purpose i have used var1...4

Requesting experts to guide me on this.

Thanks a lot.

Rgds, Anil

Re: Proc Summary - Dynamic aggregates vars

LinusH — Wed, 01 Apr 2015 06:26:46 GMT

You may need to use macro logic as well.

Re: Proc Summary - Dynamic aggregates vars

Ksharp — Wed, 01 Apr 2015 13:03:19 GMT

You can code this , and sas will rename them for you.

output out = imp1_out_data sum= /autoname ;

Re: Proc Summary - Dynamic aggregates vars

data_null__ — Wed, 01 Apr 2015 13:32:16 GMT

The statistic(var-list) syntax is usually used when you want something different from all-vars-in-var-statement or if there is no var statement.

You could have written sum(&var_aggregates)= with or without VAR statement.

Re: Proc Summary - Dynamic aggregates vars

aj34321 — Thu, 02 Apr 2015 02:01:05 GMT

Thank you so much for this simply short solution.. and I was wondering how do I iterate and append that piece of code down the proc summary.

Re: Proc Summary - Dynamic aggregates vars

aj34321 — Thu, 02 Apr 2015 02:02:09 GMT

Thank you Keshan.. Yes this also worked.

Re: Proc Summary - Dynamic aggregates vars

aj34321 — Thu, 02 Apr 2015 02:04:09 GMT

Thanks Linus, I was too thinking of appending using a macro but then other experts suggested a much better shorter way which worked for my requirement.

Re: Proc Summary - Dynamic aggregates vars

aj34321 — Thu, 02 Apr 2015 02:13:20 GMT

I may come across a requirement where there could be variety of aggregations. Assuming if i define as in below example, var_aggregates and their method, how do i build the output script. The previous logic will not work for this.

%let var_classes = class1 class2 class3 class4 class5

%let var_aggregates = var1 var2 var3 var4;

%let var_aggregates_method = N sum mean max;

proc summary data = in_data. noprint nonobs;

var &var_aggregates.;

class &var_classes.;

output out = imp1_out_data.

N(var1)=

sum(var2)=

mean(var3)=

max(var4)=

/autoname levels ways;

run;

Any suggestions..?

Thanks a lot for this great help...

Rgds, Anil

Re: Proc Summary - Dynamic aggregates vars

data_null__ — Thu, 02 Apr 2015 11:00:29 GMT

If your macro variables were considered a user interface to an application I would not use parallel lists. I see parallel lists used all the time and I don't like them even when I use them.

I would prefer something like this both as a user and a developer.

%let stat_var_aggregates = n(var1)= sum(var2)= min(var3)= max(var4)=;

Very similar and the user can see exactly what is being requested.

If you do stick with parallel lists then a macro could be used to scan the lists and output a string derived from the words. This example assumes that the lists have equal number of words although scanning stops when list1 is out of words.

%let var_classes = class1 class2 class3 class4 class5;
%let var_aggregates = var1 var2 var3 var4;
%let var_aggregates_method = N sum mean max;

%macro ArbitraryList(out=,list1=,list2=,list3=,list4=,list5=,list6=); 
   %local i j nl;
   %let nl=6;
   %let i = %eval(&i + 1);
   %do j = 1 %to &nl;
      %local w&j;
      %let w&j=%scan(%superq(list&j),&i,%str( ));
      %end; 
   %do %while(%superq(w1) ne);
      /*%put NOTE: &=i %nrbquote(%unquote(&out));*/
      %unquote(&out) 
      %let i = %eval(&i + 1);
      %do j = 1 %to &nl;
         %let w&j=%scan(%superq(list&j),&i,%str( ));
         %end; 
      %end; 
   %mend; 

page; 

%put NOTE: %ArbitraryList(out=%nrstr(&w1(&w2)=),list1=&var_aggregates_method,list2=&var_aggregates);
%put NOTE: %ArbitraryList(out=%nrstr(&w1(&w2)=&w1._&w2),list1=&var_aggregates_method,list2=&var_aggregates);
%put NOTE: %ArbitraryList(out=%nrstr(&w1(&w2)=&w1._&w2 &w3),list1=&var_aggregates_method,list2=&var_aggregates,list3=&var_classes);

56         %put NOTE: %ArbitraryList(out=%nrstr(&w1(&w2)=),list1=&var_aggregates_method,list2=&var_aggregates);
NOTE: N(var1)=              sum(var2)=              mean(var3)=              max(var4)=

57         %put NOTE: %ArbitraryList(out=%nrstr(&w1(&w2)=&w1._&w2),list1=&var_aggregates_method,list2=&var_aggregates);
NOTE: N(var1)=N_var1              sum(var2)=sum_var2              mean(var3)=mean_var3              max(var4)=max_var4

58         %put NOTE: %ArbitraryList(out=%nrstr(&w1(&w2)=&w1._&w2
58       ! &w3),list1=&var_aggregates_method,list2=&var_aggregates,list3=&var_classes);
NOTE: N(var1)=N_var1 class1              sum(var2)=sum_var2 class2              mean(var3)=mean_var3 class3              
max(var4)=max_var4 class4