In this sample we have a SAS data set that contains a list of employee numbers and we also have a master data set that contains employee numbers and their job title. This sample demonstrates how to create a list to use in a WHERE statement with the IN operator to subset the master data set based on the employee numbers from the first data set. This code creates a macro variable that contains a list of these values where each value is enclosed in quotes and separated by a blank.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

---

/* This code creates a macro variable, using SQL, that contains */
/* a quoted string to be used later within a WHERE clause using */
/* the IN operator.                                             */

data one;                                                                                                                               
  input empl $;                                                                                                                           
datalines;                                                                                                                                  
12345                                                                                                                                   
67890                                                                                                                                   
45678                                                                                                                                   
00001                                                                                                                                   
98765                                                                                                                                   
;                                                                                                                                       
                                                                                                                                        
proc sql noprint;                                                                                                                       
  select quote(trim(left(empl)))                                                                                                        
  into :list separated by ' '                                                                                                          
  from one;                                                                                                                            
quit;                                                                                                                                   
                                                                                                                                        
                                                                                                                                        
data master;                                                                                                                            
  input empl $ job $;                                                                                                                     
datalines;                                                                                                                                  
12345 clerk                                                                                                                             
34567 rep                                                                                                                               
00001 analyst                                                                                                                           
;                                                                                                                                       
                                                                                                                                        
proc print data=master;                                                                                                                 
  where empl in(&list);                                                                                                                  
run;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Obs    empl       job

 1     12345    clerk
 3     00001    analyst

This sample illustrates how to create a macro variable that contains a quoted list.

Type:	Sample
Topic:	SAS Reference ==> Macro

Date Modified:	2008-05-29 11:16:04
Date Created:	2005-09-06 13:21:27

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	Base SAS	All	n/a	n/a