proc sql;
create table dem1 as
select ex.*,
case when ex.dafl is null then adverse.dafl else ex.dafl end as date1,
case when ex.tfl is null then adverse.tfl else ex.tfl end as time1,
case when ex.pidno is null then adverse.pidno else ex.pidno end as acc1,
case when ex.folder is null then adverse.folder else ex.folder end as lbn1
from dem ex
left join
(select t1.usubjid,t1.visitnum,rsn,t1.subev,t1.visitd,t2.dafl,t2.tfl,t2.pidno,t2.folder
from
(select usubjid,visitnum,visitd,rsn,subev from dem where rsn>1) t1
join (select usubjid,visitnum,visitd,dafl,tfl,subev,pidno,folder from dem where rsn=1) t2
on t1.usubjid = t2.usubjid and t1.visitnum = t2.visitnum and t1.visitd = t2.visitd and t1.subev = t2.subev) adverse
on ex.usubjid = adverse.usubjid and ex.visitnum = adverse.visitnum and ex.visitd = adverse.visitd and ex.rsn = adverse.rsn and ex.subev = adverse.subev
;
quit;
I was wondering if I can write the code in following ways? (the differences are highlighted in bold).
1st way:
proc sql;
create table dem1 as
select dem.*,
case when ex.dafl is null then adverse.dafl else ex.dafl end as date1,
case when ex.tfl is null then adverse.tfl else ex.tfl end as time1,
case when ex.pidno is null then adverse.pidno else ex.pidno end as acc1,
case when ex.folder is null then adverse.folder else ex.folder end as lbn1
from dem
left join
(select t1.usubjid,t1.visitnum,rsn,t1.subev,t1.visitd,t2.dafl,t2.tfl,t2.pidno,t2.folder
from
(select usubjid,visitnum,visitd,rsn,subev from dem where rsn>1) t1
join (select usubjid,visitnum,visitd,dafl,tfl,subev,pidno,folder from dem where rsn=1) t2
on t1.usubjid = t2.usubjid and t1.visitnum = t2.visitnum and t1.visitd = t2.visitd and t1.subev = t2.subev) adverse
on ex.usubjid = adverse.usubjid and ex.visitnum = adverse.visitnum and ex.visitd = adverse.visitd and ex.rsn = adverse.rsn and ex.subev = adverse.subev
;
quit;
2nd way:
proc sql;
create table t1 as
select usubjid,visitnum,visitd,rsn,subev from dem
where rsn>1
create table t2 as
select usubjid,visitnum,visitd,dafl,tfl,subev,pidno,folder from dem
where rsn=1;
quit;
Proc sql;
create table adverse as
select * from t1 inner join t2
on t1.usubjid = t2.usubjid and t1.visitnum = t2.visitnum and t1.visitd = t2.visitd and t1.subev = t2.subev;
quit;
Proc sql;
create table dem1 as
select dem.*,
from dem
left join adverse
on ex.usubjid = adverse.usubjid and ex.visitnum = adverse.visitnum and ex.visitd = adverse.visitd and ex.rsn = adverse.rsn and ex.subev = adverse.subev
;
quit;
Proc sql;
create table dem2 as
select dem1.*,
case when dem.dafl is null then adverse.dafl else dem.dafl end as date1,
case when dem.tfl is null then adverse.tfl else dem.tfl end as time1,
case when dem.pidno is null then adverse.pidno else dem.pidno end as acc1,
case when dem.folder is null then adverse.folder else dem.folder end as lbn1
quit;
Why don't you try, run the different code versions and compare the results?
Hi Patrick,
I was trying to understand the concept of alias in sql. I would like to get the some suggestions of experts before I try it myself.
Thanks,
Sam.
Hi Sam
I believe you're now at the point where you should do some research, read some docu and do some experimenting on your own first.
5 seconds of Googling: http://en.wikipedia.org/wiki/Alias_(SQL)
Thanks
Patrick
Thanks Patrick.
regards,
Sam.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.