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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.