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;
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.
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
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;
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.
I'll test your proposed code...
Thanks a lot...
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.