05-04-2016 02:06 PM
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
05-04-2016 03:04 PM
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;
05-04-2016 02:23 PM
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.
05-05-2016 11:51 AM
05-05-2016 01:54 PM
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.
05-05-2016 04:27 PM
05-04-2016 03:04 PM
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;
05-05-2016 11:56 AM
this method works well and I'm able to use the output to produce what I need. thanks very much!
Need further help from the community? Please ask a new question.