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.

C Bell · Accepted Answer · 2017-05-05 16:08:01Z

0

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;

answered May 5, 2017 at 16:08

C Bell

3471 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joe Over a year ago

This is TSQL not SAS's implementation, so not relevant for this. SAS is totally different.

Joe · Accepted Answer · 2017-05-05 17:30:49Z

0

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.

answered May 5, 2017 at 17:30

Joe

63.5k7 gold badges51 silver badges70 bronze badges

3 Comments

Joe Over a year ago

I would also note that I don't find this a terribly good way to go about doing the stated task: depending on the exact details, there are lots of better ways than SQL.

BillW Over a year ago

Fantastic, Joe. This did the trick. Thank you for your help. I'm receptive to any suggestions you have about better ways to go about doing this.

Joe Over a year ago

@BillW Tom's suggestion is a start, though I think it depends too much on what you're trying to do. PROC TRANSPOSE, some of the modelling procs, or the data step may be a better solution depending on exactly what you're doing. SQL just isn't really optimized around this sort of task, and one of the major benefits of using SAS is you have lots of already-built methods for doing things like this where you don't have to basically do it by hand.

Tom · Accepted Answer · 2017-05-05 20:58:21Z

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

Collectives™ on Stack Overflow

Use SAS macro variable to create variable name in PROC SQL

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related