topic Re: data step by statement to ge the consecutive count in SAS Programming

data step by statement to ge the consecutive count

YW_CA — Thu, 10 May 2018 00:08:26 GMT

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;

Re: data step by statement to ge the consecutive count

SuryaKiran — Thu, 10 May 2018 01:18:36 GMT

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;

Re: data step by statement to ge the consecutive count

Astounding — Thu, 10 May 2018 03:13:10 GMT

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;

Re: data step by statement to ge the consecutive count

YW_CA — Fri, 11 May 2018 17:51:19 GMT

this is exactly needed. Thank you.

Re: data step by statement to ge the consecutive count

Patrick — Sat, 12 May 2018 02:18:09 GMT

@YW_CA

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