```Date: Tue, 8 Dec 2009 13:24:59 -0800 Reply-To: "Choate, Paul@DDS" Sender: "SAS(r) Discussion" From: "Choate, Paul@DDS" Subject: Re: about full join problem Comments: To: siyuan li 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 ```

Back to: Top of message | Previous page | Main SAS-L page