topic subset dataset if all records for an ID is same in SAS Programming

subset dataset if all records for an ID is same

d0816 — Mon, 08 Oct 2018 15:08:17 GMT

Hello,

I want to subset a dataset into two datasets where in one dataset I want to keep all rows for an ID if the value for another variable is a certain value.

For eg.

Dataset I have:

ID	class	status
1	1	complete
1	2	Incomplete
1	3	complete
2	1	Incomplete
2	2	Incomplete
2	3	Incomplete
3	1	Incomplete
3	2	Incomplete
4	1	complete
4	2	complete
4	3	complete
4	4	complete

Datasets I want are:

Dataset 1: status is all "Incomplete" for an ID.

ID	class	status
2	1	Incomplete
2	2	Incomplete
2	3	Incomplete
3	1	Incomplete
3	2	Incomplete

Dataset 2: status is all "Complete" or combination of "Complete/ Incomplete" for an ID.

ID	class	status
1	1	complete
1	2	Incomplete
1	3	complete
4	1	complete
4	2	complete
4	3	complete
4	4	complete

Thank you for your help.

Re: subset dataset if all records for an ID is same

novinosrin — Mon, 08 Oct 2018 15:23:23 GMT

If easy and lazy solutions are acceptable, here is one:

data have;
input ID	class	status $12.;
cards;
1	1	complete
1	2	Incomplete
1	3	complete
2	1	Incomplete
2	2	Incomplete
2	3	Incomplete
3	1	Incomplete
3	2	Incomplete
4	1	complete
4	2	complete
4	3	complete
4	4	complete
;

proc sql;
create table one as
select id, class, status
from have
group by id
having sum(status='Incomplete')=n(id)
order by id, class;
quit;

proc sql;
create table two as
select id, class, status
from have
group by id
having sum(status='complete')>0
order by id, class;
quit;

Re: subset dataset if all records for an ID is same

PGStats — Mon, 08 Oct 2018 19:08:35 GMT

Simple and fast with a data step:

data ds1 ds2;
do until(last.ID);
    set have; by id;
    if status ne "Incomplete" then found = 1;
    end;
do until(last.ID);
    set have; by id;
    if found then output ds2;
    else output ds1;
    end;
drop found;
run;