Create a new Calclation based on multiple conditions

[Ch_Jazhiel]

Hi! I've been struggling with a new calculation that I want to add to a report.

I'm creating a F1 Dataset that I want to share with my colleagues, and I want to show the most dominant Constructor Teams over the last 78 years.

Currently, I want to extract some variable values based on a nested condition. I have the following logic:

I have a Constructor_position numeric variable, with values ranging from 1 to 21 (including missing values), since this is a rank, 1 would correspond to first place, 2 to second place and so on. The issue arises when I want to extract only the constructor champion for each season. For example, if I select the 2021 Season, the Constructor Champion would be the team that has that "1" record for the Constructor_position, but I have a Race_Date Variable which has the values of each race, meaning that I would have multiple "1"s if I only extract the position itself.

Is there a way to accomplish this using the following logic:

"If Constructor_Position = 1 and Last_Race_Date by Season 

Return Constructor_Position
Else Missing"?

Or maybe I'm missing something or overcomplicating the calculation?

I Attached an example of the dataset if is of any help. In the file, Vanwall would be the constructor champion in 1958.

Thanks!

[PaigeMiller]

If I am understanding you properly, you will have to sort the data by the season and by the date of the race. Then you want to use the LAST. construct in SAS to in order to identify the last race of the season. Then it is easy to extract the information you want.

See the documentation here, and the examples will be helpful.

Many people (including me) refuse to, or cannot, download attachments. The proper way to include a data set it to provide working SAS data step code (examples and instructions).

[Ch_Jazhiel]

I did part of this and it worked, but I noticed that my logic was correct up to some point, after further investigation I had missing observations but they were related due to historical events, not missing at random.

Thank you!

[NithinRamu · Accepted solution]

Hi,

1. Create a new data item (I called it Race_Date_Numeric) with the following expression: 

TreatAs(_Number_, Race_Date). Set its aggregation to something non-additive like minimum.

2. Use this in a rank in the list table to only display the Race_Date with the highest value for Race_Date_Numeric.

This should do what you expect!

Nithin

[Ch_Jazhiel]

Thank you! This is a great example of what I was expecting as a result, however, after further investigation, I noticed that I had missing observations, but they were related due to historical events, not missing at random, however, this is a great workaround.

Thank you!