DATA Step, Macro, Functions and more

RETAIN statement

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

RETAIN statement

Hello there,

 

Let me try this one more timeSmiley Happy

 

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 8) 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,


Accepted Solutions
Solution
‎07-05-2017 02:08 PM
Super User
Posts: 5,082

Re: RETAIN statement

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.

View solution in original post


All Replies
Solution
‎07-05-2017 02:08 PM
Super User
Posts: 5,082

Re: RETAIN statement

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.

Contributor
Posts: 31

Re: RETAIN statement

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 119 views
  • 0 likes
  • 2 in conversation