topic Re: Selecting continuous sequences in SAS Procedures

Selecting continuous sequences

deleted_user — Thu, 18 Jun 2009 18:48:08 GMT

Hello SAS Users,

I have a question regarding creating a data set by selecting a sequence of observations in SAS. I have a data set similar to this:

X, Y, COUNT
20, 100, 0
20, 100, 0
20, 100, 1
20, 100, 0
20, 100, 1
20, 100, 1
20, 100, 1
20, 100, 0

21, 101, 1
21, 101, 1
21, 101, 1
21, 101, 0
21, 101, 1
21, 101, 1
21, 101, 1
21, 101, 0

22, 102, 1
22, 102, 1
22, 102, 1
22, 102, 1
22, 102, 1
22, 102, 1
22, 102, 1
22, 102, 0

Now, I need to select and output observations that have a continuous sequence of at least three 1's. For example, where X=20, Y=100, observations 5-7 should be selected; X=21, Y=101, observations 1-3 and 5-7 should be selected; X=22, Y=102, observation 1-7 should be selected. Can anybody offer any suggestions? Thanks in advance.

Re: Selecting continuous sequences

sbb — Thu, 18 Jun 2009 19:13:42 GMT

Using PROC SUMMARYwith a CLASS / BY (sorted) list, and then merge back (a DATA step MERGE with a BY and using IN=I_summary_candidate_var on the summarized file) the candidate summary-level observations back into your detail data (where non-zero condition) to select candidate detail observations.

Scott Barry
SBBWorks, Inc.

Re: Selecting continuous sequences

SushilNayak — Thu, 18 Jun 2009 20:30:35 GMT

Hey sas_grad,
Here is a different solution to the problem.

data dy;
infile datalines dlm=',' missover;
input x y cnt;
datalines;
20, 100, 0
20, 100, 0
20, 100, 1
20, 100, 0
20, 100, 1
20, 100, 1
20, 100, 1
20, 100, 0
21, 101, 1
21, 101, 1
21, 101, 1
21, 101, 0
21, 101, 1
21, 101, 1
21, 101, 1
21, 101, 0
22, 102, 1
22, 102, 1
22, 102, 1
22, 102, 1
22, 102, 1
22, 102, 1
22, 102, 1
22, 102, 0
;
run;
data dy;
set dy;
by notsorted x y cnt;
retain count 0;
if first.cnt then count=_n_;
run;

proc sql;
create table final as
select a.x, a.y, a.cnt,b.count
from dy a
,( select x, y, cnt, count , count(count) as xx
from dy
group by x,y,cnt,count
having count(count) >= 3
) b
where a.count=b.count;
quit;
proc print;run;

Thanks!!!

Re: Selecting continuous sequences

deleted_user — Mon, 22 Jun 2009 15:47:48 GMT

Thanks a bunch, Sushil and Scott. Sushil, the overall logic of your code was really helpful in solving my problem.