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!
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:
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.)(where=(&var. in (&value))
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:
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.)(where=(&var. in (&value))
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 @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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.