Date:         Tue, 15 May 2012 16:42:04 -0400
Reply-To:     "Asante, Abena" <Abena.Asante@CIBC.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Asante, Abena" <Abena.Asante@CIBC.COM>
Subject:      Re: Extract id's with multiple
In-Reply-To:  <B39864771C6F074BB21AA235089174DD0210E02C82@MBX1.myfdle.net>
Content-Type: text/plain; charset="us-ascii"

Thanks Cindy, Toby, Stephanie, Doug, Kim and everyone. Hmm, my first experience posting to the list. You're the best!

Abena

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Durrett, Cindy
Sent: Tuesday, May 15, 2012 4:07 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Extract id's with multiple

I'm sure there is a more efficient way to do this, but I would put a counter on the data to know how many occurrences there are of each ID and then output those where the counter is greater than 1.

Here's what I do (and I'm hoping the more experienced coders can also tell me how to make this better - it works but I know there must be a better way).

PROC SORT DATA=HAVE; BY ID A1; RUN;

DATA HAVECNT (KEEP=ID IDCNT);
SET HAVE;
RETAIN IDCNT 0;
BY ID A1;
IF FIRST.A1 THEN IDCNT=0;
IDCNT+1;
IF LAST.A1 THEN OUTPUT;
RUN;

(Note: I always have to do this and check the data to be sure I sorted and counted like I meant to, so I comment out the ** IF LAST.A1 THEN OUTPUT; ** and proc print some data to my output window to see that the counter has worked properly. Then I go back and run it again so it only outputs the last one which will have the number of records for that ID.)

Then I put the total count per ID back with the original data as the new variable IDCNT.  It's extremely important to test out any code where you do not overwrite your existing data :)

PROC SORT DATA=HAVECNT; BY ID; RUN;
DATA HAVEMULTIPLES;
MERGE HAVE (IN=A) HAVECNT (IN=B);
BY ID;
IF IDCNT GT 1;
RUN;


Hope this might put you on the path to getting your results.

Cindy D.

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Asante, Abena
Sent: Tuesday, May 15, 2012 3:54 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: [SAS-L] Extract id's with multiple

Dear Members,

Thank you in advance. I have a dataset that looks like this:


Id   A1     Product
1      x            11
1      y            12
2     x             11
3     z             13
4     y             12
4     x             11
5     z             13

And I want to extract those id's with multiple products so my data will look like this:

Id   A1     Product
1      x            11
1      y            12
4     y             12
4     x             11



Regards,

AA