Creating multiple new computed columns in proc SQL

Question

I want to write multiple case statements and create multiple new variables (here the new var is dob_match) being created in the same proc sql code. Can this be done? Otherwise, I am having to write multiple such proc sql code segments and then having to join these tables together. However, the tables are huge are joining 4-5 huge tables is running into space and time issues on my computer.

Can someone suggest any efficient way of doing this in SAS (each table has approc 200K rows).

Thank you.

Basically, I want to do—

Proc sql

Create table match as

( Select corc., pdd.,

Case When then else <> end as NEW_VARIABLE 1

Case When then else <> end as NEW_VARIABLE 2 . . . Case When then else <> end as NEW_VARIABLE 6

from newlink.CORC_uprob as corc
full JOIN WORK.unmatchpdd as pdd on corc.hospitalid2 = pdd.oshpdid2

);

Quit;

My SAS code is-

 proc sql; 
create
table DOB_match as 

(
select corc.Medrecn as MRN, pdd.SSN as pSSN, corc.birthdate as corcbday, pdd.bthdate as pddbday, corc.newIDCORC as newIDCORC, pdd.newIDPDD as newIDPDD, 
corc.hospitalid2 as corcHosp, pdd.oshpdid2 as pddhosp,
corc.dischargedate as dsdt_corc, pdd.dschdate as dsdt_pdd, corc.Surgdate as Surgdt_corc, pdd.PDDCABGSurgDt as Surgdt_pdd,
corc.*, pdd.*,


case 

when corc.day = pdd.day and corc.month = pdd.month and corc.year = pdd.year then 100
else -10 end as dob_match

from newlink.CORC_uprob as corc   
full JOIN WORK.unmatchpdd as pdd
on
corc.hospitalid2 = pdd.oshpdid2 ); 

quit;

What do you mean by efficient? Quick run times? Easy to create the code? Easy to maintain the code? Do you have source data for the definition of the new variables? — Tom

Mray Mray · Accepted Answer · 2018-11-06T01:27:02

I have figured it out.

    proc sql; 
   create
   table DOB_match as 

  (
 select corc.Medrecn as MRN, pdd.SSN as pSSN, corc.birthdate as corcbday,     pdd.bthdate as pddbday, corc.newIDCORC as newIDCORC, pdd.newIDPDD as   newIDPDD, 
 corc.hospitalid2 as corcHosp, pdd.oshpdid2 as pddhosp,
corc.dischargedate as dsdt_corc, pdd.dschdate as dsdt_pdd, corc.Surgdate       as Surgdt_corc, pdd.PDDCABGSurgDt as Surgdt_pdd,
corc.*, pdd.*,


case 
when corc.day = pdd.day and corc.month = pdd.month and corc.year =      pdd.year then 100
 else -10 end as dob_match,

 case 
 when corc.x = pdd.y then 100
else -10 end as test



from newlink.CORC_uprob as corc   
full JOIN WORK.unmatchpdd as pdd
 on
corc.hospitalid2 = pdd.oshpdid2 ); 

 quit;

Creating multiple new computed columns in proc SQL

1 Answers