Counts number of rows between two rows depending on criteria

[nharuka]

I'm trying to create a new column that counts the number of practice sessions between each optional practice session, but I want it to reset the counts between each two optional sessions and not aggregate. How can I do this? I know how to count it by aggregating the observations, but not how to reset them between each optional session.

data have;
  input ID $Practice;
 datalines;
1 Mandatory
2 Mandatory
3 Optional
4 Optional
5 Mandatory
6 Mandatory
7 Mandatory
8 Mandatory
9 Optional
10 Mandatory
11 Mandatory
12 Optional

I basically want a dataset that looks like this

ID Practice   Count 

1 Mandatory    1 
2 Mandatory    2
3 Optional        2
4 Optional       0
5 Mandatory    1
6 Mandatory    2
7 Mandatory    3  
8 Mandatory    4
9 Optional       4
10 Mandatory  1
11 Mandatory   2
12 Optional      2

[mkeintz · Accepted solution]

You want to:

1. Prior to checking the current practice, reset count to 0 if the preceding practice is optional.
2. Increment count by 1 if the current practice is not optional.

data want;
  set have; 
  if lag(practice)='Optional' then count=0;
  count+(practice^='Optional');
run;

v

[nharuka]

Hi V,

Your code didn't work. When I tried to use it, it gave me "." for most of the mandatory cells and then "0" for the mandatory rows that comes right after the optional rows instead. I checked my log as well and there were no error messages so I'm not sure what went wrong.

[nharuka]

Please disregard this, it worked.