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,
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.
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.
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
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.