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

[RajK]

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

[Reeza · Accepted solution]

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]

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]

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]

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