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;
... View more
