BookmarkSubscribeRSS Feed
Chang
Quartz | Level 8

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/

2 REPLIES 2
chrej5am
Quartz | Level 8

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

Astounding
PROC Star

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 386 views
  • 0 likes
  • 3 in conversation