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 (May 2008, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Thu, 22 May 2008 15:40:50 -0500
Reply-To:     Mary <>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Identifying the same pair in different order on fields
Comments: To: Chang Chung <chang_y_chung@HOTMAIL.COM>
Content-Type: text/plain; charset="iso-8859-1"

And this tells me how to fix all the data to get the smallest one to come first each time. Another brilliant SAS-L contributor!

These ID's identify blood samples from patients and sometimes the same patient gives blood again without us realizing until later that we already have an ID for them in our system, so I need to be able to identify which ones are for the same patient because I don't want to include the same patient twice in statistical analysis. Usually there are the same results, but I need to check first, then aggregate them together before running my analysis.


-Mary ----- Original Message ----- From: Chang Chung To: SAS-L@LISTSERV.UGA.EDU Sent: Thursday, May 22, 2008 3:14 PM Subject: Re: Identifying the same pair in different order on fields

On Thu, 22 May 2008 14:47:18 -0500, Mary <mlhoward@AVALON.NET> wrote:

>Hi, > >I've got two fields; ID and ALT_ID. Here is some example data: > >001-00 004-00 >099-00 001-01 >004-00 001-00 >100-00 101-00 >102-00 104-01 >101-00 100-00 >102-00 104-01 > >What I'd like to pick out are the rows in which the same pair of ID and ALT-ID occurs, but where they occur in different order > >Thus my result desired from the data above is: > >001-00 004-00 >004-00 001-00 > >100-00 101-00 >101-00 100-00 > >My desire is to pick out the ones where the ID and the Alt-ID aren't in the same order, so I can correct those in the data and put them in the same order, and thus correctly identify duplicate rows in the data. > >Thanks! > >-Mary

hi, I would consider "canonizing" id and alt_id, so that id always compares smaller than alt_id. If you have to keep the ids as they are, then create a new id and make sure that it has the same id value if their canonized id-alt_id matches. a self-join can be disastrous when there are the id-alt_id in-order duplicates. cheers, chang

/* test data */ data one; input (id alt_id) (:$6.); cards; 001-00 004-00 099-00 001-01 004-00 001-00 100-00 101-00 102-00 104-01 101-00 100-00 102-00 104-01 ; run;

data two; set one; /* canonized ids -- make id always smaller than alt_id */ if id > alt_id then do; temp = id; id = alt_id; alt_id = temp; drop temp; end; run;

proc sort data=two; by id alt_id; run;

proc print data=two; run; /* on lst Obs id alt_id 1 001-00 004-00 2 001-00 004-00 3 001-01 099-00 4 100-00 101-00 5 100-00 101-00 6 102-00 104-01 7 102-00 104-01 */

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