DATA Step, Macro, Functions and more

DATA step counting logic

Reply
Contributor
Posts: 31

DATA step counting logic

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 8) 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 SQLSmiley Happy

 

Cheers,

 

Recep
run;

Super User
Posts: 10,538

Re: DATA step counting logic

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.

Contributor
Posts: 31

Re: DATA step counting logic

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

Super User
Posts: 17,907

Re: DATA step counting logic

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;

Contributor
Posts: 31

Re: DATA step counting logic

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.

Contributor
Posts: 31

Re: DATA step counting logic

I'll test your proposed code...

 

Thanks a lot...

Ask a Question
Discussion stats
  • 5 replies
  • 125 views
  • 0 likes
  • 3 in conversation