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

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

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: 17,819

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.

View solution in original post


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

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

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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 225 views
  • 3 likes
  • 3 in conversation