Alternative process to the PROC SQL Left Join

Reply
New Contributor
Posts: 3

Alternative process to the PROC SQL Left Join

I am looking for a alternative logic by using SAS data step set key= option  to the Proc SQL lef join.

Can any one help me on this ?

below is my testing code: I could not see left join performance in final table.

data test1(index=( v0 v1));

input v0 $ v1 $ v2 $;

cards;

1 a b

2 b f

3 e f

4 r t

5 e y

6 e t

7 e y

;

run;

data test2(index=(v1));

input v1 $ nam $;

cards;

m naren

b gopi

e mahi

q nani

;

run;

data test5;

      set test1(in=a);

      set test2 key=v1;

      if _iorc_ ne 0 then do;

            _error_=0;

            call missing(nam);

      end;

run;

Occasional Contributor
Posts: 10

Re: Alternative process to the PROC SQL Left Join

Posted in reply to vnarendra9

proc sql;

create table test5 as

select a.v0,a.v1,a.v2,b.nam

from test1 as a left join test2 as b

on a.v1=b.v1;

quit;

Respected Advisor
Posts: 4,935

Re: Alternative process to the PROC SQL Left Join

Posted in reply to vnarendra9

For a datastep equivalent, try this :

data test1(index=(v0_v1=(v0 v1) v1=(v1)));
input v0 $ v1 $ v2 $;
cards;
1 a b
2 b f
3 e f
4 r t
5 e y
6 e t
7 e y
;

data test2(index=(v1));
input v1 $ nam $;
cards;
m naren
b gopi
e mahi
q nani
;

data leftJoin;
merge test1(in=in1) test2;
by v1;
if in1;
run;

proc print; run;

Note the new index on test1.

PG

PG
Ask a Question
Discussion stats
  • 2 replies
  • 276 views
  • 0 likes
  • 3 in conversation