DATA Step, Macro, Functions and more

Macro program with macro variable containing multiple values

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Macro program with macro variable containing multiple values

Hi there, 

I am using SAS 9.4.

I have multiple datasets titled as 2010q1, 2010q2, 2010q3, ..., 2015q3, 2015q4. 

Each dataset contains variables LOINC_CD, var1 var2 var3. LOINC_CD is a character variable.

I want to run a macro program so it extracts all records in all datasets where LOINC_CD takes on a list of four values: 13457-7, 18262-6, 2089-1, 55440-2.

 

This is what each dataset looks like:

 Obs    Loinc_Cd               var1         var2                var3  
 
       1    42931-6             0.0000    99999              0.000000                                            
       2    60256-5             0.0000    99999              0.000000                                            
       3    45194-8             0.0000    84702            596.000000                                     
       4    UNLOINC             0.0000    36415              0.000000                                         
       5    10331-7             0.0000    85025              0.000000                                        
       6    11546-9             0.0000    87491              0.000000                                         
       7    12235-8             0.0000    81003              0.000000                                      
       8    12710-0             0.0000    83021              0.000000                                        

 

 

So far I have created a value list and put all values into a macro variable named LOINC_LIST by doing:

data data.loinc_ldl;
input loinc $ @@;
datalines;
13457-7
18262-6
2089-1
55440-2
;

proc sql noprint;
	select loinc into :loinc_list separated by ' '
	from data.loinc_ldl;
quit;

%put loinc_list=&loinc_list;

 Here is the log:

32         %put loinc_list=&loinc_list; 
loinc_list=13457-7 18262-6 2089-1 55440-2

 

I then ran following macro program:

%macro ldlus (yr,var,value);
	data data.ldl_clms;
		set _null_;
	run;
	
	%do yr=2010 %to 2015;
		%do qr=1 %to 4;
			data data.ldl_clms;
				set data.ldl_clms in1perc.ses_lr&yr.q&qr. 
					(where=(&var.="&value") 
					keep=patid &var var1 var2 var3);
			run;
		%end;
	%end;
%mend;

%ldlus(2010,LOINC_CD,&loinc_list)

But the values are not assigned to the macro variable LOINC_CD correctly. It combines the four values into one (which doesn't exist in my datasets).

 

Here is the error log:

MPRINT(LDLUS):   set data.ldl_clms in1perc.ses_lr2010q1 (where=(LOINC_CD="13457-7 18262-6 2089-1 55440-2") keep=LOINC_CD 
var1 var2 var3);

 

Where did I do wrong?

 

Thank you very much!


Accepted Solutions
Solution
a week ago
Trusted Advisor
Posts: 1,159

Re: Macro program with macro variable containing multiple values

Posted in reply to zzfsimona

Hi @zzfsimona and welcome to the SAS Support Communities!

 

The issue is that SAS doesn't know that the string "13457-7 18262-6 2089-1 55440-2" is meant to be a list of four individual values. But this can be resolved easily in two steps:

 

  1. Modify the PROC SQL step to create a quoted list of values:
    select quote(strip(loinc)) into :loinc_list separated by ' '
    (The STRIP function is actually not necessary here. It removes leading and trailing blanks. The latter would occur here, the former could hamper the string comparison.)

  2. Adapt the WHERE condition to test if the value of LOINC_CD equals any of the four strings in the list:
    (where=(&var. in (&value))

View solution in original post


All Replies
Solution
a week ago
Trusted Advisor
Posts: 1,159

Re: Macro program with macro variable containing multiple values

Posted in reply to zzfsimona

Hi @zzfsimona and welcome to the SAS Support Communities!

 

The issue is that SAS doesn't know that the string "13457-7 18262-6 2089-1 55440-2" is meant to be a list of four individual values. But this can be resolved easily in two steps:

 

  1. Modify the PROC SQL step to create a quoted list of values:
    select quote(strip(loinc)) into :loinc_list separated by ' '
    (The STRIP function is actually not necessary here. It removes leading and trailing blanks. The latter would occur here, the former could hamper the string comparison.)

  2. Adapt the WHERE condition to test if the value of LOINC_CD equals any of the four strings in the list:
    (where=(&var. in (&value))
New Contributor
Posts: 2

Re: Macro program with macro variable containing multiple values

Posted in reply to FreelanceReinhard

It worked! Thank you Smiley Happy))

 

Also thank you @Astounding and @Reeza for your suggestions!

Super User
Posts: 6,632

Re: Macro program with macro variable containing multiple values

Posted in reply to zzfsimona

You already have a good start, that should fix the error.  A secondary issue:  the program will take 12 times the amount of time to run as it should.  The issue is the placement of the loop, and the fact that each time through the loop replaces your final data set.  This would be a faster structure to the program:

 

data data.ldl_clms;

   set 

   %do yr = 2010 to 2015;

       %do qr = 1 %to 4;

           in1perc.ses_lr&yr.&qr. (keep=patid &var var1 var2 var3)

       %end;

    %end;

    ;

  where &var in (&loinc_list);

run;

 

This assumes that you have created &LOINC_LIST using @FreelanceReinhard's suggestion.  Notice how this program reads the first data set (2010q1) only once.  Your original program read that same data 24 times.  Also notice that the standalone WHERE statement applies to all incoming data sets.

 

  

Super User
Posts: 23,296

Re: Macro program with macro variable containing multiple values

Posted in reply to zzfsimona

You can simplify this significantly by not using a macro, though you can if you want.

This uses a shortcut reference approach where you can reference all data sets with a specific prefix with a colon operator. If you still wanted a macro I'd probably use this approach to avoid the months loop and just do the year loop with the colon. 

You can keep the values list with the same approach. 

 

data want;
set in1perc.ses_lr2010: in1perc.ses_lr2011: in1perc.ses_lr2012: in1perc.ses_lr2013: in1perc.ses_lr2014:  in1perc.ses_lr2015: ;

where values in ('13457-7', '18262-6', '2089-1', '55440-2');

run;

 

 

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 182 views
  • 3 likes
  • 4 in conversation