Subject: how to identify next observations Summary: Solutions to two interpretations of the problem are given. Respondent: Ian Whitlock <whitloi1@westat.com>

Qin Pan <panq@MUSC.EDU> writes

> I have a dataset like: > obs flag > 1 1 > 2 1 > 3 1 > 4 1 > 5 0 > 6 1 > 7 0 > 8 0 > ; > I want to read the next observations, and count the number of 0's > followed by 1, if the next is 1 then count=0; > so the new data I'm looking for is like that: > obs flag count > 1 1 0 > 2 1 0 > 3 1 0 > 4 1 1 > 5 0 . > 6 1 2 > 7 0 . > 8 0 . > ;

There have been two distinct interpretations of the problem. One is to provide a sequential count of the number of 1's followed by a 0 and the other is to count the 0's following a 1. In both cases the solutions have appeared, to me, too involved for the problem.

The first problem is easily solved with a look ahead merge.

data w ; input flag ; cards ; 1 1 0 0 0 1 0 1 0 1 1 0 0 ;

data w2 ( keep = flag count ) ; merge w w ( firstobs = 2 rename = ( flag = next ) ) ; /* no by statement */ if flag = 1 and next = 0 then c + 1 ; if flag = 1 then count = c ; run ;

proc print data = w2 ; run ;

The second interpretation to count to 0's following a 1 is also solved by a merge. But this time the other set is the result of a FREQ.

data w ; input flag ; if flag = 1 then seq + 1 ; cards ; <see above> ;

proc freq data = w ( where = ( flag = 0 ) ) ; table seq / out = f ( keep = seq count ) ; run ;

data w2 ; merge w f ; by seq ; if flag = 1 and count = . then count = 0 ; else if flag = 0 then count = . ; run ;

proc print data = w2 ; run ;

Ian Whitlock