Hi,

An efficient join is a good join : it have to give you the good results ;-)

But if you want to understand which kind of joins is faster than other, you have to compare the CPU time and Elapsed time. What are these results ?

Anyway, you probably know the advantages of SQL join versus merge (and vice versa). I'll invite you to read some papers about your question :

www2.sas.com/proceedings/sugi28/096-28.pdf

www2.sas.com/proceedings/sugi29/076-29.pdf

www2.sas.com/proceedings/sugi29/042-29.pdf

Stéphane.

(Oh, I've forgotten, thanks to Lex to have added the SUGI29 into his site...)

-----Message d'origine----- De : SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] De la part de Ben Envoyé : samedi 19 février 2005 21:11 À : SAS-L@LISTSERV.UGA.EDU Objet : sql join vs sas merge

*******************************; data a; input pid dt1 date7.; datalines; 101 1jan04 201 1feb04 301 1mar05 401 1apr05 501 1apr05 601 1apr05 ; run;

data b; input pid dt2 date7.; datalines; 101 1feb04 401 1jun05 ; run;

data c; input pid dt3 date7.; datalines; 401 1jun05 501 1jun05 ; run;

data d; input pid dt4 date7.; datalines; 201 1jun05 ; run;

***merge****; data x_merge; merge a (in=a) b c d ; by pid; if a; run;

***sql**join*****;

proc sql; create table t1 as select a.pid,a.dt1,b.dt2 from a full join b on a.pid=b.pid;

create table t2 as select t.pid,t.dt1,t.dt2,c.dt3 from tmp1 as t full join c on t.pid=c.pid;

create table x_join as select t.pid,t.dt1,t.dt2,t.dt3,d.dt4 from t2 as t full join d on t.pid=d.pid;

I wonder if there is a more efficient join?