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
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
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.
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.
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.