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

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

atul_desh — Mon, 24 Oct 2016 16:02:32 GMT

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;

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

Oligolas — Mon, 24 Oct 2016 16:45:51 GMT

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

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

atul_desh — Mon, 24 Oct 2016 20:09:58 GMT

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

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

Oligolas — Tue, 25 Oct 2016 06:42:00 GMT

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;

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

Ksharp — Tue, 25 Oct 2016 09:48:31 GMT

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;

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

atul_desh — Wed, 26 Oct 2016 13:08:04 GMT

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 🙂

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

Oligolas — Wed, 26 Oct 2016 13:41:30 GMT

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;