how to get 2nd highest amount in group wise using data step and proc sql

[atul_desh]

I want to select the second highest amount for each aid i wrote below code but it is giving me error :

WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT
clause nor the optional HAVING clause of the associated table-expression referenced a
summary function

Thanks in advance

Code : 

data ds1;
input aid trndt mmddyy6. amount;
format trndt ddmmyy6.;
datalines;
1 102416 100
1 102216 600
1 102316 300
1 101016 500
2 102416 100
2 102616 600
2 102316 300
2 101016 500
run;

proc sql;
select *
from ds1
where
trndt between today() and (today()-7)
and amount in (select max(amount)from ds1 where amount not in (select max(amount)from ds1))
group by aid,amount ;
quit;

[Oligolas]

Hi,

The amount for the condition "where trndt between today() and (today()-7)" is never the second highest.

I wouldn't use SQL for this.

data ds1;
   input aid trndt mmddyy6. amount;
   format trndt ddmmyy6.;
   datalines;
   1 102416 100
   1 102216 600
   1 102316 300
   1 101016 500
   2 102416 100
   2 102616 600
   2 102316 300
   2 101016 500
run;

proc sort data=ds1; by aid descending amount; run;

data want;
   set ds1;
   by aid descending amount;
   retain counter; 
   if first.aid then counter=0;
   counter+1;
   if (today()-7)<=trndt<=today() and counter eq 2 then want=1;
run;

Cheers,

Oligolas

[atul_desh]

thanks but what if highest 'amount' was there two times then counter will give wrong result. 

[Oligolas]

OK then just catch this case

data ds1;
   input aid trndt mmddyy6. amount;
   format trndt ddmmyy6.;
   datalines;
   1 102416 100
   1 102216 600
   1 102316 300
   1 101016 500
   2 102416 100
   2 102616 600
   2 102316 300
   2 101016 500
run;

proc sort data=ds1; by aid descending amount; run;

data want;
   length lag1_amount 8;
   set ds1;
   by aid descending amount;
   retain counter; 
   lag1_amount=lag1(amount);
   if first.aid then counter=1;
   else if lag1_amount ne amount then counter+1;
   if (today()-7)<=trndt<=today() and counter eq 2 then want=1;
run;

[Ksharp]

data ds1;
input aid trndt mmddyy6. amount;
format trndt ddmmyy6.;
datalines;
1 102416 100
1 102216 600
1 102316 300
1 101016 500
2 102416 100
2 102616 600
2 102316 300
2 101016 500
;
run;
proc sql;
select * from
(
select * from ds1
except
select *
 from ds1 
  group by aid
   having amount=max(amount)
)
group by aid
 having amount=max(amount)
;
quit;



/***********/
proc sort data=ds1;
 by aid descending amount;
run;
data want;
 set ds1;
 by aid descending amount;
 if first.aid then n=0;
 if first.amount then n+1;
 if n=2;
run;

[atul_desh]

Thanks Ksharp,

but problem is I want 2nd highest from only those which happened last week "trndt between today() and (today()-7)"

if I put this above condition it won't give me any o/p becuase 2nd highest amount is not from last week.

so i'm confused like where we can put 'where' condition.

Again Thanks 🙂

[Oligolas · Accepted solution]

data ds1;
   input aid trndt mmddyy6. amount;
   format trndt ddmmyy6.;
   datalines;
   1 102416 100
   1 102216 600
   1 102316 300
   1 101016 500
   2 102416 100
   2 102616 600
   2 102316 300
   2 101016 500
run;

proc sort data=ds1; by aid descending amount; run;

DATA want;
   length lag1_amount 8;
   set ds1;
   where (today()-7)<=trndt<=today();
   by aid descending amount;
   retain counter;
   lag1_amount=lag1(amount);
   if first.aid then counter=1;
   else if lag1_amount ne amount then counter+1;
   if counter eq 2 then want=1;
   if want eq 1;
RUN;

/*KSharp's*/

PROC SQL;
   SELECT *
   FROM
     (SELECT *
      FROM ds1(
               WHERE=((today()-7)<=trndt<=today()))
      EXCEPT SELECT *
      FROM ds1(
               WHERE=((today()-7)<=trndt<=today()))
      GROUP BY aid
      HAVING amount=max(amount))
   GROUP BY aid
   HAVING amount=max(amount)
   ;
QUIT;