topic Re: Pulling ALL instances of a UniqueID when at least once instance meets required criteria in SAS Programming

Pulling ALL instances of a UniqueID when at least once instance meets required criteria

pmaloney — Mon, 27 Mar 2023 20:20:22 GMT

I'm working with meetings data that is formatted as follows:

Data: MeetingListHave

MeetingNo PersonNo RoleNo

0001 01 01

0001 02 02

0001 03 05

0002 01 01

0003 01 01

0004 01 01

0004 02 02

0004 03 02

0005 01 01

0006 01 01

0006 02 05

Every MeetingNo will always have a PersonNo 01 with RoleNo 01, but after that first person there can any number of people. Each person has exactly one role, but there can be more than one person with the same role (excluding RoleNo 01, which will always be exclusive to PersonNo 01). The available roles are 01,02,03,04,05.

I am trying to get a database where only meetings where a person with RoleNo 05 was present. I want all the PersonNos who attended that meeting, not just the person with RoleNo05. Given the dataset above I would want the final data to look like this:

Data: MeetingListWant

MeetingNo PersonNo RoleNo

0001 01 01

0001 02 02

0001 03 05

0006 01 01

0006 02 05

I've gotten as far as doing

proc sort data=meetinglisthave out=meetinglistsorted; by MeetingNo descending RoleNo; run;

so that if there is a person with the 05 role they are always in the first.meetinglistsorted position, but I don't know where to go from there.

Thanks for any help you can give, I can provide more details if needed.

Re: Pulling ALL instances of a UniqueID when at least once instance meets required criteria

SASKiwi — Mon, 27 Mar 2023 22:25:00 GMT

Something like this should give you what you want:

proc sql;
  create table want as 
  select * 
  from meetinglisthave
  where MeetingNo in
  (select MeetingNo 
   from meetinglisthave
   where RoleNo = '05'
  );
quit;

Re: Pulling ALL instances of a UniqueID when at least once instance meets required criteria

Tom — Tue, 28 Mar 2023 00:59:14 GMT

The MAX() of a series of Boolean expression is TRUE if ANY of them were true.

proc sql;
create table want as 
  select * MeetingListHave
  group by MeetingNo
  having max(RoleNo='05')
;
quit;

Re: Pulling ALL instances of a UniqueID when at least once instance meets required criteria

Kurt_Bremser — Tue, 28 Mar 2023 08:27:47 GMT

Given that your dataset is already sorted, this will outperform other methods:

data want,
do until (last.meetingno);
  set have;
  by meetingno;
  if roleno = "05" then flag = 1;
end;
do until (last.meetingno);
  set have;
  by meetingno;
  if flag then output;
end;
drop flag;
run;

Known as the "DOW loop".

Re: Pulling ALL instances of a UniqueID when at least once instance meets required criteria

pmaloney — Tue, 28 Mar 2023 15:03:40 GMT

Thank you to everyone who responded. They all worked and provided the same answers (which is nice as a redundancy check). I've chosen the answer that doesn't require a sort in case other people are looking for a similar solution in the future.

Re: Pulling ALL instances of a UniqueID when at least once instance meets required criteria

Kurt_Bremser — Tue, 28 Mar 2023 17:32:49 GMT

Note that all methods require sorting; it's just that SQL does it "under the hood". With larger datasets, comparing the methods for performance is recommended.