Solved
Contributor
Posts: 25

# Selecting top two values for a variable that has a rolling sum

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

Accepted Solutions
Solution
‎01-22-2013 10:38 AM
Super User
Posts: 23,771

## Re: Selecting top two values for a variable that has a rolling sum

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.

All Replies
Solution
‎01-22-2013 10:38 AM
Super User
Posts: 23,771

## Re: Selecting top two values for a variable that has a rolling sum

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.

Contributor
Posts: 25

## Re: Selecting top two values for a variable that has a rolling sum

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

Super Contributor
Posts: 1,636

## Re: Selecting top two values for a variable that has a rolling sum

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

Contributor
Posts: 25

## Re: Selecting top two values for a variable that has a rolling sum

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

🔒 This topic is solved and locked.