DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

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

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;

 


Accepted Solutions
Solution
‎10-26-2016 09:55 AM
Frequent Contributor
Posts: 103

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

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;

________________________

- That still only counts as one -

View solution in original post


All Replies
Frequent Contributor
Posts: 103

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

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

________________________

- That still only counts as one -

Contributor
Posts: 60

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

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

Frequent Contributor
Posts: 103

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

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;
________________________

- That still only counts as one -

Super User
Posts: 9,676

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

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;
Contributor
Posts: 60

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

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 Smiley Happy

 

Solution
‎10-26-2016 09:55 AM
Frequent Contributor
Posts: 103

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

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;

________________________

- That still only counts as one -

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 346 views
  • 0 likes
  • 3 in conversation