Date:         Tue, 25 Feb 2003 18:39:05 -0000
Reply-To:     "Femminella, Oliver" <Oliver.Femminella@HALIFAXCETELEM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Femminella, Oliver" <Oliver.Femminella@HALIFAXCETELEM.COM>
Subject:      Re: Question on merge with duplicate keys
Comments: To: 'Tomás Cámara' <tchx0507@PMICIM.COM>
Content-Type: text/plain; charset="iso-8859-1"

Thanks Tomas !
I really need to start using proc sql,
can anybody point me in the right direction (docs, webpages, etc.)
Oliver

-----Original Message-----
From: Tomás Cámara [mailto:tchx0507@PMICIM.COM]
Sent: 25 February 2003 18:32
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Question on merge with duplicate keys


Try mergin via an sql statement, since it contemplates all possible
combinations (rather than looking for single entries in one of the sets)?

The following:

proc sql;create table sortie as

select a.id,fruit,color from one as a ,two as b

where a.id=b.id;quit;

does it.

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Femminella, Oliver
Sent: Tuesday, February 25, 2003 12:19 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Question on merge with duplicate keys

Dear SAS-L,

I have a question when performing a merge of two datasets
which have duplicate keys:

DATA one;
INPUT id $ fruit $;
CARDS;
1 N
1 Y
2 N
2 Y
3 N
;
RUN;

PROC SORT data=one;
BY id;
RUN;

DATA two;
INPUT id $ color $;
CARDS;
1 100
2 50
2 20
3 80
;
RUN;

PROC SORT data=two;
BY id;
RUN;

DATA final;
merge one two;
BY id;
RUN;

The result of the above merge is:

1 N 100
1 Y 100
2 N 50
2 Y 20
3 N 80

However the desired output from a merge between dataset 'one' and 'two'
that I am looking for is the following:

1 N 100
1 Y 100
2 N 50
2 N 20
2 Y 50
2 Y 20
3 N 80

I would be grateful for any suggestions as to how I can obtain the above
result.

Cheers,
Oliver

--
Dr. Oliver Femminella
Decision support systems
Halifax Cetelem Credit Ltd.
Leo House, Railway Approach,
Wallington, Surrey SM6 0XD, U.K.
T: +44 (0)20 8254 7234
M: +44 (0)7929 981 950
F: +44(0)20 8669 8857
E: oliver.femminella@halifaxcetelem.com

This electronic message contains information from Halifax Cetelem Credit Ltd
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have received
this electronic message in error, please notify us by telephone or email (to
the numbers or address above) immediately.
This electronic message contains information from Halifax Cetelem Credit Ltd
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have received
this electronic message in error, please notify us by telephone or email (to
the numbers or address above) immediately.