BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GreggB
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

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;

art297
Opal | Level 21

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.

GreggB
Pyrite | Level 9

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

art297
Opal | Level 21

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;

GreggB
Pyrite | Level 9

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

Linlin
Lapis Lazuli | Level 10

you can get what you want by:

proc sort data=have out=want nodupkey;

by email;

run;

Linlin

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 918 views
  • 0 likes
  • 4 in conversation