LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (February 2000, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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.


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