BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
pmaloney
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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;

View solution in original post

5 REPLIES 5
SASKiwi
PROC Star

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;
Tom
Super User Tom
Super User

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;
Kurt_Bremser
Super User

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".

pmaloney
Calcite | Level 5

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. 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 421 views
  • 4 likes
  • 4 in conversation