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!
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.
How about:
%let mxlist=max(age),max(height),max(weight);
proc sql;
select &mxlist from sashelp.class;
quit;
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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: