topic Re: Proc sql join in SAS Procedures

Re: Proc sql join

sampath — Sat, 30 Nov 2013 04:41:44 GMT

Hi,

I have the following SQL code; I am trying to write the data step code similar to this. I tried some thing but in vain.Could any one help me?

I could not understand the code highlighted in bold.

Please find the sql code below.

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;

Following is my Data step code:

data t1 (keep= usubjid visitnum visitd rsn subev);

set dem ;

where rsn>1 ;

run;

data t2 (keep= usubjid visitnum visitd dafl tfl subev pidno folder);

set dem ;

where rsn=1 ;

run;

proc sort data=t1; by usubjid visitnum visitd subev; run;

proc sort data=t2; by usubjid visitnum visitd subev; run;

data adverse;

merge t1 (in=a) t2 (in=b);

by usubjid visitnum visitd subev;

if a and b;

run;

proc sort data=ex; by usubjid visitnum visitd rsn subev; run;

proc sort data= adverse; by usubjid visitnum visitd rsn subev; run;

data ex ;

merge ex (in=a) adverse (in=b);

by usubjid visitnum visitd rsn subev;

if a;

run;

data dem1 ;

set dem ;

if ex.dafl eq “ “ then date1=adverse.dafl;

else then date1=ex.dafl;

if ex.tfl eq “ “ then time1= adverse.tfl;

else then time1= ex.tfl;

if ex.pidno eq “ “ then acc1= adverse.pidno;

else then acc1= ex.pidno;

if ex.folder eq “ “ then lbn1= adverse.folder;

else then lbn1= ex.folder;

run;

Re: Proc sql join

Patrick — Sun, 01 Dec 2013 00:18:12 GMT

The select statement refers to the inline view bit.

(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

So from "outside" the join looks then like below:

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

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;

Why do you want to convert this into a data step?

I believe your last step would be:

data ex ;

merge ex (in=a) adverse (in=b);

by usubjid visitnum visitd rsn subev;

if a;

.... and here some if statements to replace the SQL Case statements...

run;

Re: Proc sql join

sampath — Sun, 01 Dec 2013 19:06:26 GMT

Thank you very much Patrick.

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

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 have some questions in the above code.

1) why are the dem ex lying side by side in the following code. What is the meaning of following code.

"from dem ex

left join

adverse?

2) I was wondering if the left join in between dem & adverse or ex & adverse?

3) What is going in to dem1? Is it the product of left join? If so why does the code have "select ex* "

4) I believe my last step would be the following code based on your suggestion.

data ex ;

merge ex (in=a) adverse (in=b);

by usubjid visitnum visitd rsn subev;

if a;

if ex.dafl eq “ “ then date1=adverse.dafl;

else then date1=ex.dafl;

if ex.tfl eq “ “ then time1= adverse.tfl;

else then time1= ex.tfl;

if ex.pidno eq “ “ then acc1= adverse.pidno;

else then acc1= ex.pidno;

if ex.folder eq “ “ then lbn1= adverse.folder;

else then lbn1= ex.folder;

run;

I always learned SQL by comparing with data step code. I could not understand the sql code that I posted here (especially the one highlighted in bold). That's why I am trying to convert this into a data step.

Thank you very much for you support.

Re: Proc sql join

Patrick — Mon, 02 Dec 2013 08:08:25 GMT

Think of this SQL this way:

proc sql;
create view adverse as
    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
;
quit;

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
          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;

In the original SQL you have an inline view defined, like: (.....) adverse

adverse is an alias for this inline view allowing you to reference it in your code. In the "outside" SQL you're then using this inline view as if it were a view defined in an earlier SQL (as done here in this post).

A construct like "from dem ex" is simply: "dem" = table name, "ex" = alias. An alias is nothing else than a placeholder for the actual table or view name so you can use it in the rest of your code. You could also use the actual table name instead.

Your last data step should create table dem1. It looks close to what you need but has still a few syntax errors in it, eg. SAS data step syntax doesn't use 2 level names like "ex.dafl". It must be "dafl" only and if the variable exists in both input data set then you need to control from which dataset it should be taken (either using a keep, drop or rename).

Re: Proc sql join

sampath — Mon, 02 Dec 2013 19:58:48 GMT

Thank Patrick.

1) I was wondering if we can modify the above code to remove ex completely? (i.e how can we can modify the above code without the use of any alias?

2) In the last step if we use keep, drop or rename the variables, how is it possible to do merging? (as are doing the merging the by common variables).