Date: Wed, 21 Feb 1996 13:23:00 -0500
Reply-To: fu.m@PG.COM
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: Min Fu <fu.m@PG.COM>
Subject: Re[2]: merging two datasets
Message authorized by:
: /S=chris@OVIEW.DEMON.CO.UK/OU=SMTP/O=1.UCN.GO.1/P=PROCTERGAMBLE/A=MCI/C=US
Sheri,
In dealing large data base, using PROC SQL will be more efficient In your
case, you certainly could use PROC SQL but you have to use COALESCE
function. The following codes has been tested to get what you want.
proc sql;
create table COMBINE as
select coalesce(O.ORIG,N.ORIG) as ORIG
,coalesce(O.DEST,B.DEST) as DEST
,O.MILE2
,N.MILE2
from DATA1 as O
full
join DATA2 as N
on (O.ORIG=N.ORIG and O.DEST=N.DEST)
order by ORIG,DEST,MILE2,MILE4
;
quit;
Min Fu
Trilogy Consulting
___________________________________________________________
Subject: Re: merging two datasets
Author: (INTERNET)SAS-L@UGA.CC.UGA.EDU at external
Date: 2/12/96 6:03 PM
Path:
auvm!paladin.american.edu!zombie.ncsc.mil!news.mathworks.com
!tank.news.pipex.ne t!pipex!demon!oview.demon.co.uk
Lines: 84
Nntp-Posting-Host: oview.demon.co.uk X-Nntp-Posting-Host:
oview.demon.co.uk X-Newsreader: WinVN 0.92.6+
Newsgroups: bit.listserv.sas-l
Organization: Oceanview Consultancy Ltd
why not just use a datastep merge?
data merged;
merge data1 data2;
by orig dest;
run;
that should produce what you want...
Chris.
------------------------------------
In article <SAS-L%96021213500414@UGA.CC.UGA.EDU>, Sheri
George <sheri@GIS712.CPA.STATE.TX.US> says:
>
>Can someone please help on the merging of two datasets?
>Here is sample data for the 2 datasets:
>
>Data1 Data2
>orig dest mile2 orig dest mile4
>-------------------- ---------------------
>Briar Austin 25.0 Briar Austin 40.0
>Killeen Dallas 7.0 Killeen Dallas 100.0
>Lee Leon 200.3 Llano Loving 30.5
>Mason Menard 45.0 Mason Menard 99.9
>Mason Menard 99.1 Mason Menard 48.0
>Moore Motley 29.0 Moore Motley 29.9
>Moore Motley 100.9
>
>What I would like the merged data set to be is:
>
>orig dest mile2 mile4
>Briar Austin 25.0 40.0
>Killeen Dallas 7.0 100.0
>Lee Leon 200.3 .
>Llano Loving . 30.5
>Mason Menard 45.0 99.9
>Mason Menard 45.0 48.0
>Mason Menard 99.1 99.9
>Mason Menard 99.1 48.0
>Moore Motley 29.0 29.9
>Moore Motley 100.9 29.0
>
>With the following code, I get everything merged correctly except--
>on those obs that appear in one data set & not the other, I get only
>the obs for the dataset listed first on the SELECT statement (the
>second listed dataset has missing values for both ORIG and DEST).
>Any suggestions?
>
>PROC SQL ;
> CREATE TABLE COMBINE AS
> SELECT * FROM DATA1 AS O FULL JOIN DATA2 AS N
> ON (O.ORIG=N.ORIG AND O.DEST=N.DEST)
> ORDER BY ORIG, DEST, MILE2, MILE4 ;
>
>The output:
> The SAS System 5
> 11:59 Monday, February 12, 1996
>
> OBS ORIG DEST MILE2 MILE4
>
> 1 . 30.5
> 2 Briar Austin 25.0 40.0
> 3 Killeen Dallas 7.0 100.0
> 4 Lee Leon 200.3 .
> 5 Mason Menard 45.0 48.0
> 6 Mason Menard 45.0 99.9
> 7 Mason Menard 99.1 48.0
> 8 Mason Menard 99.1 99.9
> 9 Moore Motley 29.0 29.9
> 10 Moore Motley 100.9 29.9
>I'm working on MVS, SAS 6.08.
>
>Thanks,
>
>Sheri George
>Texas State Comptroller, Austin TX
>e-mail sheri@gis712.cpa.state.tx.us
>phone (512) 463-4949
