storing value as macro for recall later

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

storing value as macro for recall later

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


Accepted Solutions
Solution
‎05-05-2016 11:53 AM
Super User
Posts: 19,105

Re: storing value as macro for recall later

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;

View solution in original post


All Replies
Super User
Posts: 5,362

Re: storing value as macro for recall later

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.

 

 

Occasional Contributor
Posts: 8

Re: storing value as macro for recall later

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.
Super User
Posts: 5,362

Re: storing value as macro for recall later

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.

Occasional Contributor
Posts: 8

Re: storing value as macro for recall later

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.
Solution
‎05-05-2016 11:53 AM
Super User
Posts: 19,105

Re: storing value as macro for recall later

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;
Occasional Contributor
Posts: 8

Re: storing value as macro for recall later

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 546 views
  • 5 likes
  • 3 in conversation