06-30-2017 07:06 PM
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 8) are not special teams and the consequtive rows within the same event keep counting.
input N Event Team $ Special_team $ Counter;
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
I tried the following code but it did not work for me:
proc sort data=test;
if special_team='Yes' and first.event then counter=1;
I would appreciate if you can help, especially without a SQL
06-30-2017 07:21 PM
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.
06-30-2017 07:56 PM
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.
06-30-2017 08:00 PM
As long as any special team is in the event, then you count the teams?
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;
06-30-2017 08:03 PM
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.