topic Re: Picking the first record when there's a 1-to-mnay relationship in SAS Programming

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

dxtran — Mon, 13 Sep 2021 18:29:40 GMT

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.

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

Tom — Mon, 13 Sep 2021 18:34:24 GMT

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;

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

dxtran — Mon, 13 Sep 2021 18:44:43 GMT

Thank you for the super quick response Tom!

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

Ksharp — Tue, 14 Sep 2021 12:37:40 GMT

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;

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

Tom — Tue, 14 Sep 2021 14:29:36 GMT

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.

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

Ksharp — Wed, 15 Sep 2021 11:52:54 GMT

Sure. It is depend on what OP want .