Storing a long repeated WHERE condition in a macro variable

Chang — Wed, 01 Jul 2015 14:54:00 GMT

hi all,

I am trying to create a where statement using a string value from a macro variable. My where statement is basically a repetition of "variable like 'value%' where the variable is a single variable and the value is a particular ICD-9-CM code. An example would be

where (DX1 like "196%" or DX1 like "197%" or DX1 like "198%" or DX1 like "199%" or DX1 like "40201%" or DX1 like "40211%" or DX1 like "40291%" or DX1 like "4293%" or DX1 like "425%" or DX1 like "428%" or DX1 like "3310%" or DX1 like "3311%" or DX1 like "3312%" or DX1 like "290%" or DX1 like "40311%" or DX1 like "40391%" or DX1 like "40412%" or DX1 like "40492%" or DX1 like "585%" or DX1 like "586%" or DX1 like "V420%" or DX1 like "V451%" or DX1 like "V560%" or DX1 like "V568%");

Firstly, I create a dataset combinedcx_02_weighted_diseases.sas7bat that contain 3 variables- ICD, disease and weight. The ICD variable has 692 ICD-9-CM codes. The disease variable has corresponding disease categories for the codes. Next, I try to store the very long WHERE condition in a macro variable as suggested by William 2013 (http://analytics.ncsu.edu/sesug/2014/CC-12.pdf ). Interestingly I found the WHERE condition is truncated in the SAS log by the %PUT statment. Here is how it looks. Note I mark the ending part in red. It is incomplete:

DX1 like "196%" or DX1 like "197%" or DX1 like "198%" or DX1 like "199%" or DX1 like "40201%" or DX1 like "40211%" or DX1 like "40291%" or DX1 like "4293%" or DX1 like "425%" or DX1 like "428%" or DX1 like "3310%" or DX1 like "3311%" or DX1 like "3312%" or DX1 like "290%" or DX1 like "40311%" or DX1 like "40391%" or DX1 like "40412%" or DX1 like "40492%" or DX1 like "585%" or DX1 like "586%" or DX1 like "V420%" or DX1 like "V451%" or DX1 like "V560%" or DX1 l

So there are at least two problems with my code. First, there are just 251 values in the condition. But there are 692 values in the data set to read. Next, the condition doesn't end properly. The word "like" has been split. Any idea to store the where condition in the macro variable? I have tried to increase the length of the where_string but it doesn't make a difference.

Here is my code:

The data and the program can be downloaded at

http://likecoded.com/download-2/

Re: Storing a long repeated WHERE condition in a macro variable

chrej5am — Wed, 01 Jul 2015 15:07:15 GMT

Hi,

I would say that the string becomes so long that it exceeds the maximal length for the macro variable.

I would suggest to write the code to an external file using

data _null_;

file 'temp.txt';

put "";

run;

then use the stored code via %include 'temp.txt'.

Hope it works for you,

Jakub

Re: Storing a long repeated WHERE condition in a macro variable

Astounding — Wed, 01 Jul 2015 15:42:06 GMT

Chang,

If you copied the code as it appears in the paper, you would have given WHERE_STRING a length of $5000. That's not enough to hold the logic for 692 categories. Increasing it to $15000 should do the trick in the simplest fashion.

Also note, since you are using this WHERE clause in a DATA step, there are other constructs that would work and don't involve such a long string. For example, consider:

where (DX1 like "196%" or DX1 like "197%" or DX1 like "198%");

This could be replaced with:

where DX1 in: ("196", "197", "198");

Don't forget the colon after IN.

Good luck.

topic Re: Storing a long repeated WHERE condition in a macro variable in SAS Programming

Storing a long repeated WHERE condition in a macro variable

Re: Storing a long repeated WHERE condition in a macro variable

Re: Storing a long repeated WHERE condition in a macro variable