topic Re: Dynamically coding using PROC SQL to select dynamic number of columns in SAS Programming

Dynamically coding using PROC SQL to select dynamic number of columns

Kazzie — Wed, 09 Jan 2019 10:57:09 GMT

Dear experts,

First, I am on version 7.1.

The following simple example, I would like to make it more flexible / dynamic:

PROC SQL;

SELECT           MAX(Var1)
               , MAX(Var2)
               , Max(Var3)

FROM            My_Table

;

QUIT;

To make it more dynamic:

%LET Variable_List = Var1 Var2 Var3;

PROC SQL;

SELECT MAX(&Variable_List)
FROM   My_Table
;

QUIT;

This however doesn't work, but I'd like it to function as the first manually written statement. And it should also be flexible in the way that if I give in 4 or 5 variables (var4 var5), that it will still work. This can probably be solved by using a %Do loop, but I haven't been able to make it work.

Could you give me some advice on how to do this?

Thank you!

Re: Dynamically coding using PROC SQL to select dynamic number of columns

mkeintz — Wed, 09 Jan 2019 12:14:16 GMT

How about:

%let mxlist=max(age),max(height),max(weight);
proc sql;
  select &mxlist from sashelp.class;
quit;

Re: Dynamically coding using PROC SQL to select dynamic number of columns

Astounding — Wed, 09 Jan 2019 12:53:23 GMT

If this is all you want to do, you could move away from PROC SQL:

%let Variable_List = Var1 Var2 Var3;

proc means data=my_table max;
   var &variable_list;
run;

PROC MEANS is very flexible. You can create an output data set instead of a report:

%let Variable_List = Var1 Var2 Var3;

proc means data=my_table noprint;
   var &variable_list;
   output out=want (keep=&variable_list) max=;
run;

There are also ways to keep additional variables in the output if that is a requirement. But you would need to spell out any additional requirements.

Re: Dynamically coding using PROC SQL to select dynamic number of columns

PaigeMiller — Wed, 09 Jan 2019 13:17:37 GMT

I certainly agree with @Astounding who points out that in PROC MEANS (or it's twin sister PROC SUMMARY), you don't need any looping, and provides the simplest coding.

Nevertheless, in case the problem is more complicated than the original example and it needs to be done in SQL:

%macro dothis;
    %let variable_list=var1 var2 var3;
    proc sql;
        select 
        %do i=1 %to %sysfunc(countw(&variable_list));
            %let thisvar=%scan(&variable_list,&i,%str( ));
            max(&thisvar)
            %if &i<%sysfunc(countw(&variable_list)) %then %str(,);
        %end;
        from my_table;
    quit;
%mend dothis;
%dothis

So again, you can see this is a lot of coding, and a lot of opportunity for errors, and I strongly urge you to consider the much simpler PROC MEANS/PROC SUMMARY solution.

Re: Dynamically coding using PROC SQL to select dynamic number of columns

Kazzie — Wed, 09 Jan 2019 14:05:20 GMT

Thanks for the code and your advice. I indeed accepted the solution from @Astounding

Yours however is very useful for my overall understanding!