Storing a long repeated WHERE condition in a macro variable

Reply
Contributor
Posts: 61

Storing a long repeated WHERE condition in a macro variable

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:

<paste code>

The data and the program can be downloaded at

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

Contributor
Posts: 42

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

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

Respected Advisor
Posts: 4,977

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

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.

Ask a Question
Discussion stats
  • 2 replies
  • 149 views
  • 0 likes
  • 3 in conversation