topic Re: Selecting top two values for a variable that has a rolling sum in SAS Programming

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

RajK — Tue, 22 Jan 2013 15:28:03 GMT

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

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

Reeza — Tue, 22 Jan 2013 15:38:54 GMT

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.

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

RajK — Wed, 23 Jan 2013 16:37:05 GMT

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

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

Linlin — Wed, 23 Jan 2013 18:24:33 GMT

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

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

RajK — Thu, 24 Jan 2013 04:46:15 GMT

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