Subsetting data from multiple variables

[LEINAARE]

Hello,

I have a dataset that contains multiple variables for diagnostic codes and multiple variables for procedure codes.  The dataset contains medical claims data for all medical services provided during a specified time period.  I am trying to subset a dataset which contains only family planning claims.  There are thousands of applicable diagnostic codes and hundreds of procedure codes that represent family planning claims.  I have extracted all diagnostic codes and all procedure codes into macros with codes separated by commas (ex. 'code1','code2','code3',','code4','code5', ... ,'code2000').  I created two macros, one containing diagnostic codes "&DiagCode" and one containing procedure codes "&ProcCode".

My input dataset has 21 variables that could contain diagnostic codes, and 11 variables that could contain procedure codes.  I want to subset any observation that contains any applicable diagnostic or procedure code in any of these variables.  I thought about renaming each variable to have a common prefix.  But I don't think I can use prefix lists in a where statement.  I also don't believe I can use "_CHARACTER_" or an array in a where statement.  Below is the code I created.

%let diag_var = pdiag or pdiag10 or sdiag1 or sdiag2 or sdiag3 or sdiag4 or sdiag5 or sdiag6 or sdiag7 or sdiag8 or sdiag10_1 or sdiag10_2 or sdiag10_3 or sdiag10_4 or sdiag10_5 or sdiag10_6 or sdiag10_7 or sdiag10_8 or sdiag10_9 or sdiag10_10 or admdiag;

%let proc_var = sproc1 or sproc2 or sproc3 or sproc4 or sproc5 or sproc10_1 or sproc10_2 or sproc10_3 or sproc10_4 or sproc10_5 or hcpcs;


data take_famplan;
     set medclaims;
     where &diag_var in (&DiagCode) or &proc_var in (&ProcCode);
run;

I have two questions.

1.  Will listing "var1 or var2 or var3 ... or var21" in a where statement with "in (quoted string)" actually apply the quoted string to each listed variable?  and

2.   Is there a better way to do this? 

Thanks,

Ted

[PaigeMiller · Accepted solution]

The bottom line is that you need to have valid working SAS code when the macro variables are resolved, otherwise your code with macro variables won't produce SAS code that runs.

Boolean comparisons such as you are trying to do require this syntax:

where (var1 in ('x','y','z')) or (var2 in ('x','y','z'));

and do not work (invalid syntax) like this:

where var1 or var2 in ('x','y','z'));

So your SAS code will not work.

ADVICE: Before you try to make things into a macro or use macro variables, produce code that works in SAS for a limited (but similar) problem (as above) with no macros and no macro variables. Once you have code that works, then you can begin the process of turning it into macros and/or macro variables.

Now, for your actual problem, let's suppose the problem is a bit simpler, you have only two possible diagnosis variables, and these are pdiag1 and pdiag10. The following code works:

data take_famplan;
     set medclaims;
     where (pdiag1 in (&DiagCode)) or (pdiag10 in (&DiagCode)) ;
run;

because it produces valid SAS code. So you'd have to fit your 21 diagnostic variables and 11 procedure variables into that syntax. It  is also possible to write an actual SAS macro to do this, but that might take more time than simply brute force typing the variable names as above; or use hash tables to achieve what you want (but that's not something I can help with).

Lastly, just to clear up your terminology here for greater clarity, you have not  created two macros. You have created two macro variables. Macros are not macro variables.

[LEINAARE]

Hi @PaigeMiller,

Thank you for your advice, and thank you for clarifying macro terminology for me.  I will hard code it as you suggested.

Thank you,

Ted