RETAIN statement

[Recep]

Hello there,

Let me try this one more time:)

I couldn't come up with a relatively simple counting logic and was hoping somebody can help me:

In the sample table created by the code below (which includes what I want to achive as "Desired_result") I would like to create a counter variable that starts to count teams within each "event". The only trick is, the code should start counting from 1 when a team within the event is a "special team".

For instance in the event number 6 (i.e. row 7 to 10), the "Desired_result" gets a value of 1 starting at "row_number" 9 because within that particular "event", team C (in row_number 7) and team A (row_number 😎 are not special teams thus the counter does not start to count from 1 until "row_number" 9. Then because it starts to count at "row_number" 9 it keeps counting after even though the team C at "row_number" 10 is not a special_team. After row_number 10 the counter starts from 0 again because a new "event" starts at "row_number" 11.

data test;
input Row_number Event Team $ Special_team $ Desired_result;
cards;
1 1 A No 0
2 2 B Yes 1
3 3 C No 0
4 4 B Yes 1
5 4 A No 2
6 5 A No 0
7 6 C No 0
8 6 A No 0
9 6 D Yes 1
10 6 C No 2

11 7 A No 0

12 7 C No 0

run;

I'm pretty sure I need to use a combination of first.event and RETAIN statements together but I couldn't find a solution yet. The code below did not work for me:

data test1;
set test;
by event;

retain counter;
if first.event then do;

   if special_team='Yes' then counter=1;

   else if special_team='No' then counter=0;
else counter+1;

run;

Thanks a lot in advance!

Cheers,

[Astounding · Accepted solution]

This might do it.  The hard part is figuring out the logic.  If I have it correctly:

data test1;

set test;

by event;

if first.event then counter=0;

if special_team='Yes' or counter > 0 then counter + 1;

run;

Note that you don't need to retain counter explicitly.  The statement COUNTER + 1 will automatically do that for you.

[Recep]

Thanks a lot! It worked for the sample dataset. I'll apply it to my original data to see if I have to expand the example to consider a few other scenarios...

Recep