BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RajK
Calcite | Level 5

Hi,

Could some one answer my question?

I need to find top two values for a variable (say top_car_make) that is found based on summary variable (cars_sold) over last tweleve months for each friday. Each friday we have to look back twelve months and calculate top two cars sold for a dealer. The obs are grouped as dealerID, car_make and date.

Thanks,RK

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Do you have the rolling sum portion and need to find the top two of a variable?

If so you could add a sort and then take the first two observations from your final dataset per dealer_id.

View solution in original post

4 REPLIES 4
Reeza
Super User

Do you have the rolling sum portion and need to find the top two of a variable?

If so you could add a sort and then take the first two observations from your final dataset per dealer_id.

RajK
Calcite | Level 5

Hi Reeza, Thank you for the response. I am using the following code to get top two dealers for each event date (that is each friday) but I couldn't get because the count is increasing by 1 for each dealer on the same date itself. Could you help me out with this? proc sort data=dealers; by  event_dt id descending size; run; data lib.dealers; set dealers; by  event_dt id; retain count 0; if first.event_dt and first.id then count=1; else count=count+1; run; Thanks, RK

Linlin
Lapis Lazuli | Level 10

Is the code helpful?

proc sort data=dealers;

by  id event_dt  descending size; run;

data lib.dealers;

set dealers;

by  id event_dt;

if first.event_dt then count=0;

count+1;

if count<3 then output;

run;

Message was edited by: Linlin

RajK
Calcite | Level 5

Thank you Reeza and Lillin. I sorted the data and used nested if-do loop. it worked. Thanks, Rk

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
  • 4 replies
  • 547 views
  • 3 likes
  • 3 in conversation