Picking the first record when there's a 1-to-mnay relationship

[dxtran]

Hello

I'm a super novice SAS SQL coder. I have a data set (call it test) that has that looks something like this:

ID     Name

3       John Smith

3       Andrew Smith

1       John Doe

2       Bozo the Clown

2       Felix the Cat

What I want to do is is keep just one record for ID #2 and ID #3. I don't care which record, Name could be sorted alphabetically and I could keep the first or last so I get back this:

ID     Name

1       John Doe

2       Felix the Cat

3       John Smith

I can do this in Access, but I have millions of records and I think SAS is a much better tool to do this.

Thanks in advance.

[Tom]

Just sort and use BY group processing in a data step.

data have;
  input ID Name $30.;
cards;
3 John Smith
3 Andrew Smith
1 John Doe
2 Bozo the Clown
2 Felix the Cat
;

proc sort data=have out=want;
  by id name;
run;

data want;
  set want;
  by id;
  if last.id;
run;

[dxtran]

Thank you for the super quick response Tom!

[Ksharp · Accepted solution]

data have;
  input ID Name $30.;
cards;
3 John Smith
3 Andrew Smith
1 John Doe
2 Bozo the Clown
2 Felix the Cat
;

proc sql;
create table want as
select * from have group by id having name=max(name);
quit;

[Tom]

If there multiple observations with the same value of name for an ID you could end up with multiple observations for the same value of ID.

[Ksharp]

Sure. It is depend on what OP want .