Date: Mon, 7 Feb 2000 20:52:12 GMT
Reply-To: charles_s_patridge@MY-DEJA.COM
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: charles_s_patridge@MY-DEJA.COM
Organization: Deja.com - Before you buy.
Subject: Re: many to many match merge
Hi Mike,
Try this tested code.
/*********************************************/
/*** TIP 00124 ***/
/*** Author: Charles Patridge ***/
/*** Email: Charles_S_Patridge@prodigy.net ***/
/*** ***/
/*** How to do Many to Many Merge ***/
/*** ***/
/*** Where the data looks like this: ***/
/***
tablea tableb
userid9 groupa profile99 groupa
userid4 groupa profile99 groupb
userid8 groupb profile97 groupa
userid2 groupb profile97 groupb
And the desired output needs to be:
userid9 groupa profile99
userid9 groupa profile97
userid4 groupa profile99
userid4 groupa profile97
userid8 groupb profile99
userid8 groupb profile97
userid2 groupb profile99
userid2 groupb profile97
************************************************/
Solution:
data tablea;
infile cards missover;
input @2 userid $ 7. @11 group $ 6. ;
/***tablea***/
cards;
userid9 groupa
userid4 groupa
userid8 groupb
userid2 groupb
;;;;
run;
data tableb;
infile cards missover;
input @2 profile $ 9. @12 group $ 6. ;
/***tableb***/
cards;
profile99 groupa
profile99 groupb
profile97 groupa
profile97 groupb
;;;;
run;
proc sql;
create table manymany as
select a.userid, a.group, b.profile
from tablea as a,
tableb as b
where a.group = b.group
order by a.userid, a.group, b.profile;
quit;
proc print data=manymany; run;
/*** end of Tip 00124 ***/
Regards,
Charles Patridge
In article <8525687E.0062927B.00@apollo.pheaa.org>,
Mike Swift <mswift@PHEAA.ORG> wrote:
> Hello all. I'm new to SAS so please bear with me. I want to merge
two SAS
> tables using BY processing, in which both tables have multiple
occurances of a
> variable. I've tried using the MERGE statement, but I'm not getting
the results
> that I want. I know that this can be done with PROC TRANSPOSE, as
long as I
> know what the highest COL# will be, but this will not work because the
COL#
> could, and probably will, change nightly. I've included a brief
example below.
>
> Thanks,
> Mike Swift
> PHEAA
>
> tablea tableb
> userid9 groupa profile99 groupa
> userid4 groupa profile99 groupb
> userid8 groupb profile97 groupa
> userid2 groupb profile97 groupb
>
> results(hopeful)
> userid9 groupa profile99
> userid9 groupa profile97
> userid4 groupa profile99
> userid4 groupa profile97
> userid8 groupb profile99
> userid8 groupb profile97
> userid2 groupb profile99
> userid2 groupb profile97
>
--
Charles Patridge - PDPC, Ltd.
172 Monce Road - Burlington, CT 06013
860-673-9278 or 860-675-9026
Charles_S_Patridge@prodigy.net www.sasconsig.com
Sent via Deja.com http://www.deja.com/
Before you buy.
