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;
