topic Re: Select records based on value sequence in SAS Programming

Select records based on value sequence

apolitical — Fri, 20 Jul 2018 17:56:05 GMT

I want to select records from an existing dataset based on this logic: going through each record from top to bottom, locate the record with value 0 such that all ensuing records always have value of 0, select that record and everything before that record. In the following dataset, I'm making such selection for each type value, and my desired results are records with id 4,5,8,9,10. How do I achieve this? Thanks.

id type value

1 A 1

2 A 0

3 A 1

4 A 0

5 A 0

6 B 0

7 B 1

8 B 0

9 B 0

10 B 0

Re: Select records based on value sequence

Astounding — Fri, 20 Jul 2018 18:29:56 GMT

One approach:

data want;

start_here = 0;

recnum = 0;

do until (last.id);

set have;

by id value notsorted;

recnum + 1;

if value ne 0 then start_here = 0;

else if first.value then start_here = recnum;

end;

recnum = 0;

do until (last.id);

set have;

by id;

recnum + 1;

if start_here > 0 and recnum >= start_here then output;

end;

drop recnum start_here;

run;

It's untested code, but looks like it should work.

Re: Select records based on value sequence

novinosrin — Fri, 20 Jul 2018 18:35:41 GMT

same idea, another variant:

data have;
input id   type $  value;
cards;
1    A        1
2    A        0
3    A        1
4    A        0
5    A        0
6    B        0
7    B        1
8    B        0
9    B        0
10  B        0
;

data want;
_k=.;
do _n=1 by 1 until(last.type);
set have;
by type;
if value then _k=_n;
end;
do _n_=1 by 1 until(last.type);
set have;
by type;
if  _n_>_k then output;
end;
drop _:;
run;

Re: Select records based on value sequence

novinosrin — Fri, 20 Jul 2018 18:54:00 GMT

data have;
input id   type $  value;
cards;
1    A        1
2    A        0
3    A        1
4    A        0
5    A        0
6    B        0
7    B        1
8    B        0
9    B        0
10  B        0
;
proc sql;
create table want(drop=_:) as
select * 
from (select *,value=1 as _v, max(id) as _m from have group by type,_v)
group by type
having id>max((_v=1)*_m)
order by id, type;
quit;

Re: Select records based on value sequence

Ksharp — Sat, 21 Jul 2018 11:43:20 GMT

If I understood what you mean.

data have;
input id   type $  value;
cards;
1    A        1
2    A        0
3    A        1
4    A        0
5    A        0
6    B        0
7    B        1
8    B        0
9    B        0
10  B        0
;
run;

data temp;
 set have;
 by type value notsorted;
 group+first.value;
 if value=0;
run;
data want;
 set temp;
 by type group;
 if first.type then _group=0;
 _group+first.group;
 if _group ne 1 ;
 drop group _group;
run;

Re: Select records based on value sequence

apolitical — Mon, 23 Jul 2018 18:31:28 GMT

I'm sure all suggested answers work fine. I chose the one I could understand the most easily as the answer. Thank you all.