Use SAS macro variable to create variable name in PROC SQL

Question

I'm trying to create a set of flags based off of a column of character strings in a data set. The string has thousands of unique values, but I want to create flags for only a small subset (say 10). I'd like to use a SAS macro variable to do this. I've tried many different approaches, none of which have worked. Here is the code that seems simplest and most logical to me, although it's still not working:

%let Px1='12345'; 

PROC SQL;

CREATE TABLE CLAIM1 AS
SELECT 

b.MEMBERID
, b.ENROL_MN
, CASE WHEN (a.PROCEDURE = &Px1.) THEN 1 ELSE 0 END AS CPT_+&Px1.  
, a.DX1
, a.DX2
, a.DX3
, a.DX4

FROM ENROLLMENT as b
left join CLAIMS as a
on a.MEMBERID = b.MEMBERID;

QUIT;

Obviously there is only one flag in this code, but once I figure it out the idea is that I would add additional macro variables and flags. Here is the error message I get:

8048  , CASE WHEN (PROCEDURE= &Px1.) THEN 1 ELSE 0 END AS CPT_+&Px1.
                                                           -
                                                           78
ERROR 78-322: Expecting a ','.

It seems that the cause of the problem is related to combining the string CPT_ with the macro variable. As I mentioned, I've tried several approaches to addressing this, but none have worked.

Thanks in advance for your help.

Answer 1

Something like this normally requires dynamic sql (although I am not sure how will that works with SAS, I believe it may depend on how you have established connection with the database).

Proc sql;

DECLARE @px1 varchar(20) = '12345'
       ,@sql varhcar(max) = 
               'SELECT b.MEMBERID
                     , b.ENROL_MN
                     , CASE WHEN (a.PROCEDURE = ' + @Px1 + ') THEN 1 ELSE 0 
                                  END AS CPT_' + @px1  + '
                     , a.DX1
                     , a.DX2
                     , a.DX3
                     , a.DX4

                   FROM ENROLLMENT as b
                   left join CLAIMS as a
                   on a.MEMBERID = b.MEMBERID'

EXEC sp_excutesql @sql;



QUIT;

Answer 2

Your issue here is the quotes in the macro variable.

%let Px1='12345';

So now SAS is seeing this:

... THEN 1 ELSE 0 END AS CPT_+'12345'

That's not remotely legal! You need to remove the '.

%let Px1 = 12345;

Then add back on at the right spot.

CASE WHEN a.procedure = "&px1." THEN 1 ELSE 0 END AS CPT_&px1.

Note " not ' as that lets the macro variable resolve.

Answer 3

If you have a list it might help to put the list into a table. Then you can use SAS code to generate the code to make the flag variables instead of macro code.

Say a table with PX code variable.

data pxlist;
  input px $10. ;
cards;
12345 
4567
; 

You could then use PROC SQL query to generate code to make the flag variable into a macro variable.

proc sql noprint;
select catx(' ','PROCEDURE=',quote(trim(px)),'as',cats('CPT_',px))
  into :flags separated by ','
  from pxlist
;
%put &=flags;
quit;

Code looks like

PROCEDURE= "12345" as CPT_12345,PROCEDURE= "4567" as CPT_4567

So if we make some dummy data.

data enrollment ;
  length memberid $8 enrol_mn $6 ;
  input memberid enrol_nm;
cards;
1 201612
;
data claims;
  length memberid $8 procedure $10 dx1-dx4 $10 ;
  input memberid--dx4 ;
cards;
1 12345 1 2 . . . 
1 345 1 2 3 . .
;

We can then combine the two tables and create the flag variables.

proc sql noprint;
create table want as
  select *,&flags
  from ENROLLMENT 
  natural join CLAIMS
;
quit;

Results

memberid procedure dx1 dx2 dx3 dx4 enrol_mn CPT_12345 CPT_4567
1        12345     1   2           201612   1         0
1        345       1   2   3       201612   0         0