Programming the statistical procedures from SAS

Proc Summary - Dynamic aggregates vars

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Proc Summary - Dynamic aggregates vars

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


Accepted Solutions
Solution
‎04-01-2015 09:32 AM
Respected Advisor
Posts: 3,780

Re: Proc Summary - Dynamic aggregates vars

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.

View solution in original post


All Replies
Super User
Posts: 5,311

Re: Proc Summary - Dynamic aggregates vars

You may need to use macro logic as well.

Data never sleeps
Frequent Contributor
Posts: 76

Re: Proc Summary - Dynamic aggregates vars

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.

Super User
Posts: 9,769

Re: Proc Summary - Dynamic aggregates vars

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

output out = imp1_out_data   sum=  /autoname ;

Frequent Contributor
Posts: 76

Re: Proc Summary - Dynamic aggregates vars

Thank you Keshan.. Yes this also worked.

Solution
‎04-01-2015 09:32 AM
Respected Advisor
Posts: 3,780

Re: Proc Summary - Dynamic aggregates vars

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.

Frequent Contributor
Posts: 76

Re: Proc Summary - Dynamic aggregates vars

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.

Frequent Contributor
Posts: 76

Re: Proc Summary - Dynamic aggregates vars

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

Respected Advisor
Posts: 3,780

Re: Proc Summary - Dynamic aggregates vars

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


🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 476 views
  • 3 likes
  • 4 in conversation