The Language of SAS - I constructed an IN list from a dataset, but now it breaks my SQL WHERE clause because it is too long

The FAQ Can I create a macro variable longer than 65,534 bytes? is useful for creating a list longer than the 65K character limit imposed by a macro variable, and was used in that example to demonstrate how an IN list might be constructed from an existing dataset.

However if the IN list generated is 'too' long, then the WHERE clause in an SQL query can break (although a comparable WHERE statement in a DATA STEP does not).

To resolve this the WHERE clause can be broken down into a number of smaller clauses based on the number of observations in the source data, and conditionally dropped into the SQL query.

data _null_ ;
 file "&workpath\zipcodes_split.sas" lrecl = 800 pad ;
 set sashelp.zipcode 
     end = lastobs 
     ;
   if _n_ = 1 then 
   do ;
     put '%macro zipcodes_split.sas ;' ;
     put 'zip_code in (' ;
   end ;
   put zip @ ;
   if not lastobs then 
   do ;
     if mod(_n_,10000) = 0 then put ') or zip_code in (' ;
     else put ',' @ ;
   end ; 
   else
   do ;
     put ')' ;
     put ;
     put '%mend zipcodes_split.sas ;' ;
   end ;
run ;
%include "&workpath\zipcodes_split.sas" ;

As previously the macro program call can then be dropped into a WHERE clause:

...

  where %zipcodes_split

...

The only caveat is that the macro program should encode the variable name (in this case zip_code )to be used in the WHERE clause.

The MOD value can be varied to increase / reduce the length of the phrases, e.g. setting it to 10 results in a macro program like:

Zipcodes macro syntax

...

Zipcodes macro syntax