BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zzfsimona
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

4 REPLIES 4
FreelanceReinh
Jade | Level 19

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))
zzfsimona
Fluorite | Level 6

It worked! Thank you :)))

 

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

Astounding
PROC Star

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.

 

  

Reeza
Super User

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;

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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