SAS-L archives -- March 2010, week 1 (#447)

Date: Sun, 7 Mar 2010 13:01:32 -0500 Reply-To: art297@NETSCAPE.NET Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU> From: Arthur Tabachneck <art297@NETSCAPE.NET> Subject: Re: Many-to-many merge Comments: To: joelleahallak@gmail.com In-Reply-To: <164b7e301003070852g17a20210jc666a6f8993c7361@mail.gmail.com> Content-Type: text/plain; charset="utf-8"; format=flowed

Joelle,

There is a time delay (sometimes a large time delay) between when posts are appear on the listserv and Google group's copy of the listserv. I can't recall on which I had placed my original post.

However, your problem doesn't appear to be a many-to-many merge but, rather, a straight forward merge using both id and visno. e.g.,:

data dataset1; input ID VISNO Varx $; cards; 1 1 N 1 2 N 1 3 Y 1 4 N 1 5 N 2 1 N 2 2 Y 2 3 N 2 4 N 2 5 N ;

data dataset2; input ID VISNO Vary $; cards; 1 0 N 1 3 N 1 4 Y 1 6 N 1 8 N 2 0 N 2 4 Y 2 6 N 2 8 N 2 10 N ;

proc sort data=dataset1; by ID VISNO;run; proc sort data=dataset2; by ID VISNO;run;

data want; Merge dataset1 dataset2; By ID VISNO; run;

HTH, Art -----Original Message----- From: Joelle Hallak <joelleahallak@gmail.com> To: Arthur Tabachneck <art297@netscape.net> Sent: Sun, Mar 7, 2010 11:52 am Subject: Re: Many-to-many merge

Hi Arthur, for some reason I do not see your question posted on the discussion page. Anyway, i posted my answer to your question there

I should have changed VarX to Var Y in dataset2. The two datasets do not have variables in common.

I want the final dataset to look something like this ID Ā Ā VISNO Ā Ā Varx Ā Ā Ā Vary 1 Ā Ā Ā Ā 0 Ā Ā Ā Ā Ā Ā . Ā Ā Ā Ā Ā N 1 Ā Ā Ā Ā 1 Ā Ā Ā Ā Ā Ā N Ā Ā Ā Ā Ā . 1 Ā Ā Ā Ā 2 Ā Ā Ā Ā Ā Ā N Ā Ā Ā Ā Ā . 1 Ā Ā Ā Ā 3 Ā Ā Ā Ā Ā Ā Y Ā Ā Ā Ā N 1 Ā Ā Ā Ā 4 Ā Ā Ā Ā Ā Ā N Ā Ā Ā Ā Y 1 Ā Ā Ā Ā 5 Ā Ā Ā Ā Ā Ā N Ā Ā Ā Ā . 1 Ā Ā Ā Ā 6 Ā Ā Ā Ā Ā Ā . Ā Ā Ā Ā Ā N 1 Ā Ā Ā Ā 8 Ā Ā Ā Ā Ā Ā . Ā Ā Ā Ā Ā N

Thanks,

On Sun, Mar 7, 2010 at 10:39 AM, Arthur Tabachneck <art297@netscape.net> wrote: jiji,

Given the sample data you provided, what do you want the resulting file to look like? Ā There are many possibilities and, for both ids, visno 4 has conflicting values for varx.

Art ------------- On Mar 7, 10:56Ā am, jiji <joelleahal...@gmail.com> wrote: > Hi, > > I am having difficulty with a many-to-many merge between two > datasets. > The two datasets actually have 2 variables in common (ID and VISNO), > but I want to merge on ID only since I would lose some data from the > first dataset if I merge with ID and VISNO. Here are both my merge and > procSQL codes: > > proc sort data=dataset1; by ID; > proc sort data=dataset2; by ID; > Data want; > Merge > dataset1 (IN=in1) > dataset2; > By ID; > if in1; > Run; > > OR > > PROC SQL; > CREATE TABLE want AS > SELECT * > FROM dataset1,dataset2 > WHERE dataset1.ID=dataset2.ID > ; > QUIT; > > Here is a snapshot of how the data looks: > dataset1: > > ID Ā Ā VISNO Ā Ā Ā Varx > 1 Ā Ā Ā Ā 1 Ā Ā Ā Ā Ā Ā N > 1 Ā Ā Ā Ā 2 Ā Ā Ā Ā Ā Ā N > 1 Ā Ā Ā Ā 3 Ā Ā Ā Ā Ā Ā Y > 1 Ā Ā Ā Ā 4 Ā Ā Ā Ā Ā Ā N > 1 Ā Ā Ā Ā 5 Ā Ā Ā Ā Ā Ā N > 2 Ā Ā Ā Ā 1 Ā Ā Ā Ā Ā Ā N > 2 Ā Ā Ā Ā 2 Ā Ā Ā Ā Ā Ā Y > 2 Ā Ā Ā Ā 3 Ā Ā Ā Ā Ā Ā N > 2 Ā Ā Ā Ā 4 Ā Ā Ā Ā Ā Ā N > 2 Ā Ā Ā Ā 5 Ā Ā Ā Ā Ā Ā N > > dataset2: > > ID Ā Ā VISNO Ā Ā Ā Varx > 1 Ā Ā Ā Ā 0 Ā Ā Ā Ā Ā Ā N > 1 Ā Ā Ā Ā 3 Ā Ā Ā Ā Ā Ā N > 1 Ā Ā Ā Ā 4 Ā Ā Ā Ā Ā Ā Y > 1 Ā Ā Ā Ā 6 Ā Ā Ā Ā Ā Ā N > 1 Ā Ā Ā Ā 8 Ā Ā Ā Ā Ā Ā N > 2 Ā Ā Ā Ā 0 Ā Ā Ā Ā Ā Ā N > 2 Ā Ā Ā Ā 4 Ā Ā Ā Ā Ā Ā Y > 2 Ā Ā Ā Ā 6 Ā Ā Ā Ā Ā Ā N > 2 Ā Ā Ā Ā 8 Ā Ā Ā Ā Ā Ā N > 2 Ā Ā Ā 10 Ā Ā Ā Ā Ā Ā N

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

listserv.uga.edu
Enterprise Information Technology Services
The University of Georgia
listhelp@uga.edu