## data step by statement to ge the consecutive count

Solved
Occasional Contributor
Posts: 19

# data step by statement to ge the consecutive count

need to keep track of each player's play time. for example:

Play HOUR PLAYED_YN

1       1              Y

1       2              Y

1       3              N

1       4              N

1       5              N

1       6              Y

I'd like to see

Play HOUR PLAYED_YN   UP_TIME DOWN_TIME

1       1              Y                 1

1       2              Y                 2

1       3              N                                     1

1       4              N                                     2

1       5              N                                     3

1       6              Y                 1

I want to keep count the consecutive hours a player played. somehow the returning results are looking weird

data temp;
infile datalines delimiter=',';
input player \$ hour played_yn\$;
datalines;
A,1,Y
A,2,Y
A,3,N
A,4,N
A,5,N
;
run;
proc sort data=temp;by player hour played_yn;run;

data count;
set temp;

by player hour played_yn;

if first.player and first.played_yn then do;
if played_yn = 'Y' then UP_TIME = 1;
if played_yn = 'Y' then DOWN_TIME = 1;
end;

if played_yn = 'Y' then up_time = up_time+1;
if played_yn = 'N' then down_time = down_time+1;

if last.player or last.played_yn then output;
run;

Accepted Solutions
Solution
‎06-08-2018 05:55 PM
Super User
Posts: 6,778

## Re: data step by statement to ge the consecutive count

[ Edited ]

It's a little difficult to tell what variables you want to work with, but I think this does the trick:

data want;

set have;

by player played_yn notsorted;

if first.played_yn then do;

up_time = .;

down_time = .;

end;

if played_yn='Y' then up_time + 1;

else down_time + 1;

run;

This should at least get you from the top data set that you posted to the bottom data set.  Still, it outputs every observation.  You may want to insert something like this at the end:

if last.played_yn then output;

All Replies
Valued Guide
Posts: 596

## Re: data step by statement to ge the consecutive count

Since Hours are distinct values and the sort order you used makes every played_yn as First.

``````data temp;
infile datalines delimiter=',';
input player \$ hour played_yn\$;
datalines;
A,1,Y
A,2,Y
A,3,N
A,4,N
A,5,N
A,6,Y
B,1,Y
B,2,Y
B,3,N
B,4,N
B,5,N
;
run;
proc sort data=temp;
by player descending played_yn hour;
run;

data count;
retain UP_TIME DOWN_TIME;
set temp;
by player descending played_yn hour;
Lag_Hour=Lag(Hour);
IF 		(first.played_yn and played_yn='Y')
or  (played_yn='Y' and Hour-Lag_Hour^=1)
then DO;
UP_TIME=1;
DOWN_TIME=.;
END;
ELSE IF played_yn='Y' THEN DO;
UP_TIME+1;
DOWN_TIME=.;
END;
ELSE IF (first.played_yn and played_yn='N')
or  (played_yn='N' and Hour-Lag_Hour^=1)
then DO;
UP_TIME=.;
DOWN_TIME=1;
END;
ELSE IF played_yn='N' THEN DO;
UP_TIME=.;
DOWN_TIME+1;
END;
run;``````
Thanks,
Suryakiran
Solution
‎06-08-2018 05:55 PM
Super User
Posts: 6,778

## Re: data step by statement to ge the consecutive count

[ Edited ]

It's a little difficult to tell what variables you want to work with, but I think this does the trick:

data want;

set have;

by player played_yn notsorted;

if first.played_yn then do;

up_time = .;

down_time = .;

end;

if played_yn='Y' then up_time + 1;

else down_time + 1;

run;

This should at least get you from the top data set that you posted to the bottom data set.  Still, it outputs every observation.  You may want to insert something like this at the end:

if last.played_yn then output;

Occasional Contributor
Posts: 19

## Re: data step by statement to ge the consecutive count

this is exactly needed. Thank you.

Posts: 4,736

## Re: data step by statement to ge the consecutive count

@YW_CA

If you've got the solution then please mark the answer which has given you "the best" answer as solution.

☑ This topic is solved.