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
– Tom, Commented Nov 4, 2018 at 16:16

Mray · Accepted Answer · 2018-11-06 01:27:02Z

0

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;

answered Nov 6, 2018 at 1:27

Mray

631 silver badge9 bronze badges

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

Collectives™ on Stack Overflow

Creating multiple new computed columns in proc SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related