General SAS Programming

[ka2yee]

Hello,

I have a dataset that has 5 possible values for a variable I call comorb_cat2.  I want to count up the total number of records with a given comorb_cat2 say the value 7 and store this number in macro.  I can easily do this for a single value using the following code (this code works):

proc sql noprint;
 select count(*) into:total
 from work.add_joint_cat
 where comorb_cat2=7
quit;
%put &total;

However, I would like to do this same step with the other 4 values for comorb_cat2.  I wrote a do loop, but for some reason I am not getting any total counts being stored in the macros I assigned.  Here is my stab at writing the code:

/*create global macros*/
 %LET LBL9=0;
 %LET LBL10=7;
 %LET LBL11=8;
 %LET LBL12=9;
 %LET LBL13=10;

%macro loop;
%do j=9 %to 13;
proc sql noprint;
 select count(*) into :denom_&&LBL&j
 from work.add_joint_cat
 where comorb_cat2=&&LBL&j;
quit;
%end;
%mend loop;
%loop

%put _user_;

I'm using SAS 9.4 TS Level 1M2 x64_7PRO platform under OS Windows version 6.1.7601

Any help would be much appreciated.  Thanks!

canuck

[Reeza · Accepted solution]

Consider not using this method at all. 

Your counting a value, so use proc freq to get the counts and then a data _null_ step to create the global macro variables.

proc freq data=add_joint_cat noprint;
table comorb_cat2/out=counts;
where comorb_cat2 in (0, 7, 8, 9, 10);
run;

data _null_;
set counts;
call symputx('lbl'||put(_n_, 2. -l), count, 'g');
run;

[Astounding]

Unfortunately, SQL always uses the local symbol table when it creates new macro variables.  So all your &DENOM_ variables are local.  The easiest fix to define them as global would be to add this statement after the %DO statement:

%global denom_&&LBL&J;

On a side note, it would be possible to define the %DO loop as generating just the SELECT statements.  The PROC SQL statement could be moved before the %DO, and the QUIT statement after the %END.

Good luck.

[ka2yee]

Hi,

Thanks a lot for the feedback. I tried adding the single line as suggested but I get a symbolic reference not resolved. It appears the macro continues past LBL13, which I can't understand.


/*create global macros*/

%LET LBL9=0;

%LET LBL10=7;

%LET LBL11=8;

%LET LBL12=9;

%LET LBL13=10;

/*double check global macros created and can be read in log*/

%put _user_;
%macro loop;
%do j=9 %to 13;
proc sql noprint;
select count(*) into :denom_&&LBL&j
from work.add_joint_cat
where comorb_cat2=&&LBL&j;
quit;
%end;
%global denom_&&LBL&j;
%mend loop;
%loop

NOTE: PROCEDURE SQL used (Total process time):
real time 0.11 seconds
cpu time 0.10 seconds

NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.09 seconds

NOTE: PROCEDURE SQL used (Total process time):
real time 0.07 seconds
cpu time 0.07 seconds

NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.09 seconds

NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.09 seconds

WARNING: Apparent symbolic reference LBL14 not resolved.
ERROR: Invalid symbolic variable name &.



________________________________
This message and any attached documents are only for the use of the intended recipient(s), are confidential and may contain privileged information. Any unauthorized review, use, retransmission, or other disclosure is strictly prohibited. If you have received this message in error, please notify the sender immediately, and then delete the original message. Thank you.

[Astounding]

To get the %GLOBAL statement to work, you would have to insert it after the %DO statement (not after the %END statement).

Note that Reeza's solution will work for the case that you are looking at.  The key is that the incoming codes are in sorted order.  If they were not, you could run into trouble matching up with the proper &DENOM variable.

[ka2yee]

Awesome this worked! Thank you.

________________________________
This message and any attached documents are only for the use of the intended recipient(s), are confidential and may contain privileged information. Any unauthorized review, use, retransmission, or other disclosure is strictly prohibited. If you have received this message in error, please notify the sender immediately, and then delete the original message. Thank you.

[Reeza · Accepted solution]

Consider not using this method at all. 

Your counting a value, so use proc freq to get the counts and then a data _null_ step to create the global macro variables.

proc freq data=add_joint_cat noprint;
table comorb_cat2/out=counts;
where comorb_cat2 in (0, 7, 8, 9, 10);
run;

data _null_;
set counts;
call symputx('lbl'||put(_n_, 2. -l), count, 'g');
run;

[ka2yee]

this method works well and I'm able to use the output to produce what I need.  thanks very much!