Hello, there:
I want to generate macro variable, PARAMN1-PARAMN5, and I am trying to convert repeated structure of my code into macro.
But can't get macro variables I want. Please let me know if you know why this problem happens and how to resolve.
Thank you in advance.
data test;
do paramn=1 to 5 by 1;
do subjid=1 to 5 by 1;
output;
end;
end;
run;
* Separated SELECT Clause <-- n.p. ;
proc sql noprint;
select count(distinct subjid) into :_nobs_1
from work.test(where=(paramn eq 1))
;
select count(distinct subjid) into :_nobs_2
from work.test(where=(paramn eq 2))
;
quit;
%put &_nobs_1.;
%put &_nobs_2.;
* in macro <-- Problem!! ;
proc sql noprint;
%macro gen_nobs(paramn=);
select count(distinct subjid) into :_nobs_¶mn.
from work.test(where=(paramn eq ¶mn.))
;
%mend gen_nobs;
%gen_nobs(paramn=1)
%gen_nobs(paramn=2)
%gen_nobs(paramn=3)
%gen_nobs(paramn=4)
%gen_nobs(paramn=5)
quit;
%put &_nobs_1.;
%put &_nobs_2.;
%put &_nobs_3.;
%put &_nobs_4.;
%put &_nobs_5.;
Please try the below code
%macro gen_nobs(paramn=);
%global _nobs_¶mn.;
proc sql noprint;
select count(distinct subjid) into :_nobs_¶mn.
from work.test(where=(paramn eq ¶mn.))
;quit;
%mend gen_nobs;
%gen_nobs(paramn=1)
%gen_nobs(paramn=2)
%gen_nobs(paramn=3)
%gen_nobs(paramn=4)
%gen_nobs(paramn=5)
%put &_nobs_1.;
%put &_nobs_2.;
%put &_nobs_3.;
%put &_nobs_4.;
%put &_nobs_5.;
The problem may be that the macro variables created in SQL are by default local to the macro. So they will not exist once you exit the macro. This can be solved by declaring the macro variables %GLOBAL in the macro.
Another possibility is to create all the variables in one fell swoop, like this:
proc sql noprint;
select count(distinct subjid) into :_nobs_1-:_nobs_999
from test
group by paramn
order by paramn;
quit;
If you do not know the exact number of PARAMN variables, or their values, it may be a good idea to get their values into macro variables as well:
proc sql noprint;
select paramn,count(distinct subjid) into :param1-:param999,:_nobs_1-:_nobs_999
from test
group by paramn
order by paramn;
quit;
You can then use the automatic macro variable SQLOBS to find the number of distinct PARAMN values:
data _NULL_;
do _N_=1 to &sqlobs;
p=symget(cats('param',_N_));
n=symget(cats('_nobs_',_N_));
put p= n=;
end;
run;
Hi @s_lassen
May I suggest a small change: It the counting is done in the proc sql step, but the creation of macro variables is done in a data step, there is no need for an upper limit to the number of macro variables.
* Make some test data with different numbers of subjects
and a dublet record to test for distinct counting;
data test;
do paramn=1 to 5 by 1;
do subjid=1 to paramn+3 by 1;
output;
end;
end;
paramn = 5; subjid = 3; output;
run;
* Distinct count of parameters and subjects;
proc sql noprint;
create table a as
select distinct paramn, count(distinct subjid) as nobs
from work.test
group by paramn;
quit;
* Place in macro variables;
data _null_; set a end=eof;
n = left(put(_N_,8.));
call symputx('_nobs_'||strip(n), nobs);
call symputx('param_'||strip(n), paramn);
put paramn= nobs=;
if eof then call symputx('param_cnt',n);
run;
%put &=param_cnt;
* Test existence and value of macro variables;
%macro test;
%do i = 1 %to ¶m_cnt;
%put parameter: &¶m_&i - subjects: &&_nobs_&i;
%end;
%mend;
%test;
I do not think there is that much of a problem with the "upper limit", you can just as well use a larger number. Only the macro variables actually written to are created anyway.
I think the SQL solution is much simpler and more elegant. It can easily be modified to use the exact number of values, if that is required (the disadvantage is slightly slower performance):
proc sql noprint;
select count(distinct paramn) into :param_cnt trimmed from test;
select count(distinct subjid) into :_nobs_1-:_nobs_¶m_cnt
from test
group by paramn
order by paramn;
quit;
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.