topic Re: Can a macro variable be a list of constants? in SAS Programming

Can a macro variable be a list of constants?

chuakp — Tue, 29 Apr 2014 19:17:54 GMT

I have a patient dataset where variables dx_code1-dx_code41 represent 41 diagnosis code variables. My objective is to output only observations in which at least one of the 41 diagnosis codes matches a particular code. I have 20 codes in mind, and I am trying to find an efficient way to program this in SAS.

I am trying to set up a macro variable that is a list of constants, but the following code does not work:

%let codes = '78060', '7784', '4659', '07999', '46611', '77989', '5990', '53081', '27651', '77189', '44619', '77982', '77931', '7746', 'V290', '77182', '7755', 'V070', '6910', '78791';

data patients;

array a_dx dx_code1-dx_code41;

do j = 1 to 20;

flag = 0;

do i = 1 to dim(a_dx);

if a_dx(i) in: codes(j) then flag = 1;

end;

if flag > 0 then output;

drop i flag;

end;

run;

Any suggestions? Thanks!

Re: Can a macro variable be a list of constants?

ballardw — Tue, 29 Apr 2014 19:27:39 GMT

I think you're looking for a line like:

if a_dx in (&codes) then flag= 1.

or flag = (a_dx in (&codes);

However the values you have in the macrovariable codes are not all numeric so the dx_code variables better be character AND have the cases match, ie V070 not v070.

Re: Can a macro variable be a list of constants?

jwillis — Tue, 29 Apr 2014 19:33:45 GMT

Hi,

I solved the problem using a proc format style. I found it was easier to update and change the formats than the macros. Also the format style provided me with the "OTHER" option.

Re: Can a macro variable be a list of constants?

chuakp — Tue, 29 Apr 2014 19:38:41 GMT

Thanks for the replies. I apologize, but I just realized that my initial question was not clear. Ideally, my code would produce twenty datasets. The first dataset would be any patients where one of the 41 diagnosis codes was '78060', the second dataset would be any patients where one of the 41 diagnosis codes was '7784', etc.

How would I do this?

Re: Can a macro variable be a list of constants?

Reeza — Tue, 29 Apr 2014 19:38:50 GMT

proc format is generally considered the better approach from the last time I posted a question on here.

Also, be careful with the in: as the matches may not be exactly what you want.

Re: Can a macro variable be a list of constants?

chuakp — Tue, 29 Apr 2014 19:43:08 GMT

How would I use PROC FORMAT to solve this problem?

Re: Can a macro variable be a list of constants?

jwillis — Wed, 30 Apr 2014 13:20:48 GMT

Hi chuakp,

Below is one style of coding. Coding the various possible values of diagnosis codes saves the need for using the upper, lower or proper case functions. If you have to add codes or change the output locations of codes, it is easier to manipulate the proc format than the data step code. The notfound and error statements are redundant. I added them just to show you what is possible. If a new 'type' is ever added to SASHELP.CARS, the notfound dataset would contain any rows with the new type.

proc format;
value $diagx
     'SEDAN','Sedan','sedan'        = 1
     'SUV','Suv','suv'                      = 2
     'SPORTS','Sports','sports'    = 3
     'WAGON','Wagon','wagon'    = 4
     'TRUCK','Truck','truck'           = 5
     'HYBRID','Hybrid','hybrid'      = 6
      OTHER                                  = 7
run;;

data want1 want2 want3 want4 want5 want6 notfound error;

set sashelp.cars;

if put(type,$diagx.) = 1 then output want1;

else if put(type,$diagx.) = 2 then output want2;

else if put(type,$diagx.) = 3 then output want3;

else if put(type,$diagx.) = 4 then output want4;

else if put(type,$diagx.) = 5 then output want5;

else if put(type,$diagx.) = 6 then output want6;

else if put(type,$diagx.) = 7 then output notfound;

else output error;

run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

354:5 355:10 356:10 357:10 358:10 359:10 360:10

NOTE: There were 428 observations read from the data set SASHELP.CARS.

NOTE: The data set WORK.WANT1 has 262 observations and 15 variables.

NOTE: The data set WORK.WANT2 has 60 observations and 15 variables.

NOTE: The data set WORK.WANT3 has 49 observations and 15 variables.

NOTE: The data set WORK.WANT4 has 30 observations and 15 variables.

NOTE: The data set WORK.WANT5 has 24 observations and 15 variables.

NOTE: The data set WORK.WANT6 has 3 observations and 15 variables.

NOTE: The data set WORK.NOTFOUND has 0 observations and 15 variables.

NOTE: The data set WORK.ERROR has 0 observations and 15 variables.

NOTE: DATA statement used (Total process time):

real time 1.21 seconds

cpu time 0.20 seconds

Re: Can a macro variable be a list of constants?

Kurt_Bremser — Wed, 30 Apr 2014 13:39:40 GMT

Everytime you have a succession of else if ..., you might consider using select:

select (put(type,$diagx.));

when (1) output want1;

...

when (7) output notfound;

otherwise output error;

end;

It's easier to read, and more efficient, because it has to do the put function only once.

Re: Can a macro variable be a list of constants?

Kurt_Bremser — Wed, 30 Apr 2014 13:47:10 GMT

How about this:

data

diags_78060

.....

diags_78791

;

set patients;

array a_dx dx_code1-dx_code41;

do i = 1 to dim(a_dx);

select (a_dx(i));

when ('78060') output diags_78060;

.....

when ('78791') output diags_78791;

otherwise;

end;

run;

Re: Can a macro variable be a list of constants?

jwillis — Wed, 30 Apr 2014 14:18:57 GMT

Hi Kurt,

Thank you for providing your suggestion! My style suggestion is based on the "Comparisons" paragraph in the definition of the SELECT statement of the Dictionary of SAS statements in the SAS 9.3 Statements Reference book. Page 343. My style has become habit and I traditionally work with smaller datasets.

Re: Can a macro variable be a list of constants?

chuakp — Wed, 30 Apr 2014 14:32:24 GMT

Hi Kurt, the code you provided above gives me fewer observations for each output dataset than the following code that I have been using:

data diag1; set main;

array dx dx_code1-dx_code41;

flag=0;

do i=1 to dim(dx);

if dx(i) in: ('78060') then flag=1;

end;

if flag>0 then output;

drop i flag;

run;

I ended up putting the above code into a macro and typing 20 macro lines, changing the output dataset and the diagnosis code so that I get SAS to search the 41 diagnosis code fields for the first diagnosis code and output it to data set 1, then search the 41 diagnosis code fields for the second diagnoss code and output it to data set 2. It seems inefficient but I guess it will work.

Re: Can a macro variable be a list of constants?

Reeza — Wed, 30 Apr 2014 14:35:57 GMT

Are you familiar with BY processing?

You usually don't need to separate the diagnosis into separate files, unless its for final output.

Your code uses the in: (in with colon) where 78060 will also match 780605 or 78060.3 for example, while Kurt's will only match exactly 78060.

Re: Can a macro variable be a list of constants?

chuakp — Wed, 30 Apr 2014 14:37:50 GMT

Hi jwillis, I tried this approach, but the problem is that some of the values in the 41 diagnosis code variables are character variables like 'E19', and you can't use a PUT statement for them. Most of the values are 5-digit numbers like 93819 but SAS trips up on the character variables.

Re: Can a macro variable be a list of constants?

Kurt_Bremser — Wed, 30 Apr 2014 14:39:26 GMT

I quota reeza's post:

Also, be careful with the in: as the matches may not be exactly what you want.

Re: Can a macro variable be a list of constants?

chuakp — Wed, 30 Apr 2014 14:54:45 GMT

Thanks Reeza - since I'm only searching for one code, I changed to an equals sign instead of (in with colon). Even after these change, the number of observations do not change in my output datasets. Since diagnosis codes are five digits maximum (e.g., there is no 78060.5 value), I would not expecting that making this change would make a difference for five-digit codes like 78060. In theory, a four-digit code like 7784 could be problematic if it matched both 77841 and 07784 (although empirically when I change to an equals sign, the number of observations don't change either).

The reason I am wanting 20 output datasets is that I then am going to run proc freq's on each of them individually.

Re: Can a macro variable be a list of constants?

jwillis — Wed, 30 Apr 2014 14:56:05 GMT

Hi chuakp,

I've encountered your issue in the past. I'll see if I can dig up the code I wrote. The problem I had was that SAS was interpreting the 'read from' input diagnosis code variable as numeric. I was reading from EXCEL and the first 10 diagnosis positions were numeric so SAS said all the values in the EXCEL column were numeric. The format statement should handle the E19 code if you code all the values in quotes. If you can post the error portion of your log or the proc contents output of your input dataset, I will be better able to advise you.

Re: Can a macro variable be a list of constants?

chuakp — Wed, 30 Apr 2014 14:57:05 GMT

Kurt, you and Reeza make a good point. But that does not explain the discrepancy between the number of observations between the code I am now using (30933 observations) and the code you suggested (31082 observations).

data diag1; set main;

array dx dx_code1-dx_code41;

flag=0;

do i=1 to dim(dx);

if dx(i) = '78060' then flag=1;

end;

if flag>0 then output;

drop i flag;

run;

Re: Can a macro variable be a list of constants?

RW9 — Wed, 30 Apr 2014 14:57:57 GMT

Well, you could try the below, one thing I would check is what should happen if the code appears in more than one variable, say col1 and col2 both have 46611, should that row output twice or once? The below outputs twice, however if you want it only once then change the if with the embedded output, to a select/when as KurtBremser has suggested. The main perk with the below code is that it generates the code based on the data, so if you have lots of codes in a dataset, and lots of variables, the code doesn't need to change you can use that to generate the code (in this example I put the list in the do loop, however you could as easy have it in a dataset and put that I as a set).

data test;
attrib code1 code2 code3 code4 format=$20.;
code1='7784'; code4='46611'; output;
code2='7784'; code4='46611'; output;
code1='7784'; code2='5990'; output;
run;

proc sql;
select distinct NAME
into    :VAR_LIST separated by '","'
from    SASHELP.VCOLUMN
where   LIBNAME="WORK"
      and MEMNAME="TEST";
quit;

data temp;
do i= '78060','7784','4659','07999','46611','77989','5990';
    call execute('data code_'||strip(i)||';
                    set work.test;');
    do j="&VAR_LIST.";
      call execute('if '||strip(j)||'="'||strip(i)||'" then output;');
    end;
    call execute('run;');
end;
run;

Re: Can a macro variable be a list of constants?

chuakp — Wed, 30 Apr 2014 15:01:24 GMT

Hi jwillis, this is the code that I used based on your suggestion. The proc format contains the 20 codes of interest - I want SAS to search the 41 diagnosis code variables for these 20 codes.

In the log, I essentially get a deluge of the following error statements:

NOTE: Invalid numeric data, 'E9' , at line 3613 column 8.

NOTE: Invalid numeric data, 'V1' , at line 3613 column 8.

etc.

proc format;

value $ dxcodes

'78060' = 1

'7784' = 2

'4659' = 3

'07999' = 4

'46611' = 5

'77989' = 6

'5990' = 7

'53081' = 8

'27651' = 9

'77189' = 10

'46619' = 11

'79982' = 12

'77931' = 13

'7746' = 14

'V290' = 15

'77182' = 16

'7755' = 17

'V070' = 18

'6910' = 19

'78791' = 20;

run;

data want1 want2 want3 want4 want5 want6 want7 want8 want9 want10 want11 want12 want13 want14 want15 want16 want17 want18 want19 want20;

set main;

array dx dx_code1-dx_code41;

flag=0;

do i=1 to dim(dx);

if put(dx(i), $dxcodes.)= 1 then flag=1;

end;

if flag>0 then output;

drop i flag;

run;

Re: Can a macro variable be a list of constants?

chuakp — Wed, 30 Apr 2014 15:04:27 GMT

Hi RW9, diagnosis codes should not appear in more than one of the 41 diagnosis code fields. Even if they did, I would only want the row to output once. Still, Kurt's code and my code are giving slightly different numbers.