putting row values into a list

[ilikesas]

 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!

[PGStats]

Do both operations at once:

PROC SQL;
select * from have
group by id
having max(num=1)=1;
quit;

[ilikesas]

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 · Accepted solution]

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]

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]

change the where statement in your last step to:

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

[Reeza]

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