Alias in Proc sql join:

[sampath]

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;

[Patrick]

Why don't you try, run the different code versions and compare the results?

[sampath]

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.

[Patrick]

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

[sampath · Accepted solution]

Thanks Patrick.

regards,

Sam.