Date:         Tue, 8 Dec 2009 13:24:59 -0800
Reply-To:     "Choate, Paul@DDS" <Paul.Choate@DDS.CA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Choate, Paul@DDS" <Paul.Choate@DDS.CA.GOV>
Subject:      Re: about full join problem
Comments: To: siyuan li <lisiyuan0753@GMAIL.COM>
In-Reply-To:  <98a376ec-ca19-4276-ad08-eb5b8b627b81@u18g2000pro.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

As Toby said - SQL and the data step work differently - and while it's not to hard to make a data step mimic a SQL join the reverse isn't always so easy.

The key is the uniqueness of the rows - so the most straightforward solution is to make your rows unique...

data a;
input code manager $;
cards;
145 max
145 xam
155 paul
165 kobe
;

data b;
input code assistant $;
cards;
145 jerry
145 tracy
145 wade
155 chen
170 yi
;

data a;
     set a;
     by code;
     if first.code then n=0;
     n+1;
run;

data b;
     set b;
     by code;
     if first.code then n=0;
     n+1;
run;

data C_DATASTEP(drop=n);
     merge a b;
     by code n;
run;

proc sql;
  create TABLE C_SQL as
     select COALESCE(a.code,b.code) as code,
            a.manager, b.assistant
     from a full join b
     on a.code=b.code and a.n=b.n;
quit;



Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of siyuan li
Sent: Tuesday, December 08, 2009 4:44 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: about full join problem

hi, I am sorry first for that I ask the same problem.
here has 2 datasets:
  data a;
input code manager$;
cards;
145 max
145 xam
155 paul
165 kobe
;
run;
data b;
input code assistant$;
cards;
145 jerry
145 tracy
145 wade
155 chen
170 yi
;
run;
data c;
merge a b;
by code;
run;
the question is that I want to yield the same output to use proc sql
full join procedure, I try to use the procudure below,but it did not
yield the same output,can anyone help me?
 proc sql;
select a.code,a.manager,b.assistant from a full join b
on a.code=b.code;
quit;

thanks in advance