data step by statement to ge the consecutive count

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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;

     

View solution in original post


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

Posted in reply to Astounding

this is exactly needed. Thank you.

Respected Advisor
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 158 views
  • 0 likes
  • 4 in conversation