topic Re: Macro program with macro variable containing multiple values in SAS Programming

Macro program with macro variable containing multiple values

zzfsimona — Sun, 10 Jun 2018 06:22:37 GMT

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!

Re: Macro program with macro variable containing multiple values

FreelanceReinh — Sun, 10 Jun 2018 10:36:59 GMT

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:

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.)
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))
```

Re: Macro program with macro variable containing multiple values

Astounding — Sun, 10 Jun 2018 12:20:53 GMT

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;

;

where &var in (&loinc_list);

run;

This assumes that you have created &LOINC_LIST using @FreelanceReinh'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.

Re: Macro program with macro variable containing multiple values

Reeza — Sun, 10 Jun 2018 19:07:33 GMT

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;

Re: Macro program with macro variable containing multiple values

zzfsimona — Mon, 11 Jun 2018 03:48:31 GMT

It worked! Thank you :)))

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