First I want to get the distinct list of the variable.
Here let's say I have 4 different levels for var.
But what if I have unknown different levels? Is there a way to generate the macro variables on the fly?
proc sql; select distinct var into :v1 -:v4 from t; quit;
Then for each of them, I want to loop over a series of macros.
For example
%macroone(a = &v1.); %macrotwo(a = &v1.);
Is there such a thing as a macro array where I can loop using the position of the array?
Such as
%macroone(a= &v[1].);
Instead of storing your distinct values (how can you guarantee there are only four?) in macro variables, store them in a new dataset, and then do a data _null_ step from that, and use call execute to call your macro for every distinct item. That scales indefinitely.
Call execute. See the second example in the documentation.
Instead of storing your distinct values (how can you guarantee there are only four?) in macro variables, store them in a new dataset, and then do a data _null_ step from that, and use call execute to call your macro for every distinct item. That scales indefinitely.
Make your macro self-contained, so that it works as a "black box" (no side effects, all parameters appear in the macro definition). Names of output datasets should be derived from the parameters or supplied as a separate parameter.
Then you store all needed parameter values in the dataset and do
data _null_;
set control_dataset;
call execute('%macro_call(param1='!!strip(param1)!!',param2='!!strip(param2)!!');');
run;
Be aware that macro logic will be executed as soon as you place the macro in the queue with call execute (while your data _null_ is still running), but all data and proc steps created from that will only start after the data _null_ has finished. So you should not create new macro variables in data steps for further use within the macro.
And finally, often operations like you seem to have in mind can be handled with by group processing, without the need for macro processing at all.
@afiqcjohari wrote:
Can you elaborate more on the group processing? Is it another feature of SAS?
By-group processing is one of THE features of SAS.
Compare this
%macro means(group);
data class;
set sashelp.class;
where sex = "&group";
run;
proc means data=class;
var age;
output out=class_&group;
run;
%mend;
proc sql noprint;
select count(distinct(sex)) into :varcount from sashelp.class;
quit;
%let varcount=%sysfunc(strip(&varcount));
proc sql noprint;
select distinct(sex) into :var1 - :var&varcount from sashelp.class;
quit;
%macro run_all;
%do i = 1 %to &varcount;
%means(&&var&i);
%end;
%mend;
%run_all;
With this:
proc sort data=sashelp.class out=class;
by sex;
run;
proc means data=class;
by sex;
var age;
output out=class_means;
run;
You can put the complete list into a single macro variable (as long as none of the values contain embedded blanks). Then process each item in the list. Here are the basics:
Without knowing the number of levels of a variable create means output for each level
inspired by
https://goo.gl/8Bf1ap
https://communities.sas.com/t5/Base-SAS-Programming/How-to-create-a-list-of-macro-variables-using-proc-SQL/m-p/341449
HAVE
====
Up to 40 obs from sashelp.class total obs=19
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Alice F 13 56.5 84.0
3 Barbara F 13 65.3 98.0
4 Carol F 14 62.8 102.5
5 Henry M 14 63.5 102.5
6 James M 12 57.3 83.0
7 Jane F 12 59.8 84.5
8 Janet F 15 62.5 112.5
9 Jeffrey M 13 62.5 84.0
10 John M 12 59.0 99.5
11 Joyce F 11 51.3 50.5
12 Judy F 14 64.3 90.0
13 Louise F 12 56.3 77.0
14 Mary F 15 66.5 112.0
15 Philip M 16 72.0 150.0
16 Robert M 12 64.8 128.0
17 Ronald M 15 67.0 133.0
18 Thomas M 11 57.5 85.0
19 William M 15 66.5 112.0
WANT ( proc means listing and output dataset by levels of variable)
=====================================================================
Up to 40 obs from CLASS_M total obs=5
Obs _TYPE_ _FREQ_ _STAT_ AGE
1 0 10 N 10.0000
2 0 10 MIN 11.0000
3 0 10 MAX 16.0000
4 0 10 MEAN 13.4000
5 0 10 STD 1.6465
Up to 40 obs from CLASS_F total obs=5
Obs _TYPE_ _FREQ_ _STAT_ AGE
1 0 9 N 9.0000
2 0 9 MIN 11.0000
3 0 9 MAX 15.0000
4 0 9 MEAN 13.2222
5 0 9 STD 1.3944
Sex=F
Analysis Variable : AGE
N Mean Std Dev Minimum Maximum
------------------------------------------------------------------
9 13.2222222 1.3944334 11.0000000 15.0000000
------------------------------------------------------------------
Sex=F
Analysis Variable : AGE
N Mean Std Dev Minimum Maximum
------------------------------------------------------------------
10 13.4000000 1.6465452 11.0000000 16.0000000
------------------------------------------------------------------
WORKING CODE
============
select quote(max(sex)) into :sex separated by ","
do grp=&sex;
call symputx('grp',grp);
dosubl
proc means data=sashelp.class(where=(sex="&grp"));
output out=class_&grp.
FULL SOLUTION
=============
proc datasets lib=work kill;
run;quit;
%symdel grp sex;
data _null_;
if _n_=0 then do;
%let rc=%sysfunc(dosubl('
proc sql noprint;
select quote(max(sex)) into :sex separated by ","
from sashelp.class group by sex
;quit;
'));
end;
do grp=&sex;
call symputx('grp',grp);
rc=dosubl('
proc means data=sashelp.class(where=(sex="&grp"));
title "Sex=&grp";
var age;
output out=class_&grp.;
run;quit;
');
end;
stop;
run;quit;
SYMBOLGEN: Macro variable GRP resolves to F
NOTE: There were 9 observations read from the data set SASHELP.CLASS.
WHERE sex='F';
NOTE: The data set WORK.CLASS_F has 5 observations and 4 variables.
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.09 seconds
SYMBOLGEN: Macro variable GRP resolves to M
NOTE: There were 10 observations read from the data set SASHELP.CLASS.
WHERE sex='M';
NOTE: The data set WORK.CLASS_M has 5 observations and 4 variables.
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.06 seconds
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.