BookmarkSubscribeRSS Feed
Recep
Quartz | Level 8

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;

5 REPLIES 5
ballardw
Super User

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.

Recep
Quartz | Level 8

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

Reeza
Super User

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;

Recep
Quartz | Level 8

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.

Recep
Quartz | Level 8

I'll test your proposed code...

 

Thanks a lot...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 911 views
  • 0 likes
  • 3 in conversation