DATA step counting logic

[Recep]

Hello there,

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 "Counter") 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), counter gets a value of 1 only at row number 9 as within the same event team C (in row 7) and team A (row 😎 are not special teams and the consequtive rows within the same event keep counting.

data test;
input N Event Team $ Special_team $ Counter;
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

run;

I tried the following code but it did not work for me:

proc sort data=test;
by event;

data test1;
set test;
by event;
if special_team='Yes' and first.event then counter=1;
else counter+1;

run;

I would appreciate if you can help, especially without a SQL:)

Cheers,

Recep
run;

[ballardw]

Do you have list of "special" teams? How do you have it? How many are there?

And if B is not a special team how/why does the counter get set to 1 for: 2 2 B Yes 1

And if this

4 4 B Yes 1  <- again why is this counter 1
5 4 A No 2

I think you may need to expand on your example some to provide a couple more examples of where the count starts and if order matters explicitly describe the rules a bit more.

[Recep]

The column "special_team" tells about it (Yes vs. No). I hope the SAS code I included produces the right data for you.

B is a special team.

Cheers,

Recep

[Reeza]

As long as any special team is in the event, then you count the teams? 

Untested

Proc sort data=have;
By event DESCENDING special_team;

Data want;
Set have;

By event;

If first.event and special_team='Yes' then team=1;
Else if not first.event and team > = 1 then team+1;
Else team=0;

Run;

[Recep]

That's correct. That's when I start to count from 1 and continue within the same event without checking whether the consecutive team is special or not.

[Recep]

I'll test your proposed code...

Thanks a lot...