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

 hi,

 

suppsoe I have the following table:

 

id num
1 0
2 0
2 0
3 1
3 0
4 1
4 0
5 1

 

First I would like to get the rows where the id has a num = 1 and this is easily done:

 

data have1;

set have;

if num = 1;

run;

 

and I get the following data:

 

id num
3 1
4 1
5 1

 

Now what I want is to get the ids 3,4 and 5 into a list like this when I run the following pseudo-code:

 

data want;

set have;

if id in list;       I know this line has a wrong syntax, its just for the purpose of illustration

run; 

 

I get the final data:

 

id num
3 1
3 0
4 1
4 0
5 1

 

So this data has all the original ids that have a num=1  

 

Thnaks!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Use SQL and IN with a sub-query. 

 

PROC SQL;
Select *
From have
Where id in (select distinct ID from have where num=1);
Quit;

View solution in original post

6 REPLIES 6
PGStats
Opal | Level 21

Do both operations at once:

 

PROC SQL;
select * from have
group by id
having max(num=1)=1;
quit;
PG
ilikesas
Barite | Level 11

Hi PGStats,

 

thanks for the reply, but is it possible to have a solution with the list becasue the example that I have put is an oversimplification and my real data is more complicated than that, and it will be really helpful if I could create a data table by inputting a list.

 

Thank you!

Reeza
Super User

Use SQL and IN with a sub-query. 

 

PROC SQL;
Select *
From have
Where id in (select distinct ID from have where num=1);
Quit;
ilikesas
Barite | Level 11

Hi Reeza,

 

I managed to put the values in a list in the following way:

 

proc sql;
select /*distinct*/ id
into :id separated by ', '
from have1
;

 

Then I wanted to use your code, which works greatly as it is, to do something like this:

PROC SQL;
Select *
From have
Where id in ( select &id);
Quit;

 

But I got an error message. Is it even possible to use list macro variables in this way?

 

Thank you!

Shmuel
Garnet | Level 18

change the where statement in your last step to:

where ID in (&id)        /* drop the "select" */

Reeza
Super User

You can, but you don't gain anything by using macro variables in this case. 

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!

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
  • 6 replies
  • 2068 views
  • 4 likes
  • 4 in conversation