topic Re: putting row values into a list in SAS Programming

putting row values into a list

ilikesas — Sat, 17 Sep 2016 20:07:58 GMT

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!

Re: putting row values into a list

PGStats — Sat, 17 Sep 2016 20:11:34 GMT

Do both operations at once:

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

Re: putting row values into a list

ilikesas — Sat, 17 Sep 2016 20:25:12 GMT

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!

Re: putting row values into a list

Reeza — Sat, 17 Sep 2016 22:39:23 GMT

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;

Re: putting row values into a list

ilikesas — Sun, 18 Sep 2016 00:38:34 GMT

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!

Re: putting row values into a list

Shmuel — Sun, 18 Sep 2016 00:56:13 GMT

change the where statement in your last step to:

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

Re: putting row values into a list

Reeza — Sun, 18 Sep 2016 01:00:29 GMT

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