topic Re: finding duplicates in SAS Procedures

finding duplicates

GreggB — Tue, 03 Jan 2012 19:15:20 GMT

i have data as seen below. Instructors have a unique email address but may have multiple IDs if they serve more than one school. The restriction on the file I'm submitting is only one ID per instructor. I want to find all cases such as hsmith@yahoo.com and give him the same ID in all obs. (It doesn't matter which one as long as he only has one.) Right now, I'm running a PROC FREQ with a TABLE statement of email*tchid/list and doing a visual inspection (not exactly an elegant solution.)

email tchid

jdoe@aol.com 01

sjones@hotmail.com 02

hsmith@yahoo.com 03

hsmith@yahoo.com 04

Re: finding duplicates

Tom — Tue, 03 Jan 2012 19:20:23 GMT

You could use SQL. For example you could decide to pick the smallest id for each email address.

(NOTE: are you sure that the different schools are using different ranges of id values? Otherwise you might have the same id for two different instructors.)

proc sql ;
  create table uniqueid as 
   select distinct email, min(tid) as tid 
   from mutlipleid
   group by email
  ;
quit;

Re: finding duplicates

art297 — Tue, 03 Jan 2012 19:23:02 GMT

or, if you prefer a datastep solution:

data have;

informat email $40.;

input email tchid $;

cards;

jdoe@aol.com 01

sjones@hotmail.com 02

hsmith@yahoo.com 03

hsmith@yahoo.com 04

;

proc sort data=have (drop=tchid) out=want;

by email;

run;

data want (drop=temp:);

set want;

by email;

if first.email then tempid+1;

tchid=put(tempid,z2.);

run;

That could, of course, be modified to retain some of the existing ids.

finding duplicates

GreggB — Tue, 03 Jan 2012 19:26:44 GMT

this would assign new IDs, wouldn't it? I have to use the ones that are already in the student information system.

finding duplicates

GreggB — Tue, 03 Jan 2012 19:27:52 GMT

schools are using different ranges. they are assigned at a central location to ensure there's no overlap.

finding duplicates

art297 — Tue, 03 Jan 2012 19:32:19 GMT

Easy to accomodate:

data have;

informat email $40.;

input email tchid $;

cards;

jdoe@aol.com 01

sjones@hotmail.com 02

hsmith@yahoo.com 03

hsmith@yahoo.com 04

;

proc sort data=have out=want;

by email;

run;

data want (drop=hold:);

set want;

by email;

retain holdid;

if first.email then holdid=tchid;

else tchid=holdid;

run;

Re: finding duplicates

Linlin — Tue, 03 Jan 2012 19:32:41 GMT

you can get what you want by:

proc sort data=have out=want nodupkey;

by email;

run;

Linlin