topic Re: Counts number of rows between two rows depending on criteria in SAS Programming

Counts number of rows between two rows depending on criteria

nharuka — Sun, 20 Mar 2022 03:30:51 GMT

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

Re: Counts number of rows between two rows depending on criteria

mkeintz — Sun, 20 Mar 2022 04:23:57 GMT

You want to:

Prior to checking the current practice, reset count to 0 if the preceding practice is optional.
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;

Re: Counts number of rows between two rows depending on criteria

nharuka — Sun, 20 Mar 2022 19:08:32 GMT

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.

Re: Counts number of rows between two rows depending on criteria

nharuka — Sun, 20 Mar 2022 19:10:36 GMT

Please disregard this, it worked.