How Can I Generate Macro Variables in proc SQL appropriately?

[KentaMURANAKA]

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_&paramn.
    from work.test(where=(paramn eq &paramn.))
    ;
%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.;

[Jagadishkatam]

Please try the below code

%macro gen_nobs(paramn=);
%global _nobs_&paramn.;
proc sql noprint;
    select count(distinct subjid) into :_nobs_&paramn.
    from work.test(where=(paramn eq &paramn.))
    ;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.;

[s_lassen]

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;

[ErikLund_Jensen]

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 &param_cnt;
		%put parameter: &&param_&i - subjects: &&_nobs_&i;
	%end;
%mend;
%test;

[s_lassen]

Hi @ErikLund_Jensen 

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_&param_cnt
  from test 
  group by paramn
  order by paramn;
quit;