Solved
Contributor
Posts: 66

# 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

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
Regular Contributor
Posts: 164

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

________________________

- Cheers -

All Replies
Regular Contributor
Posts: 164

## 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

________________________

- Cheers -

Contributor
Posts: 66

## 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.

Regular Contributor
Posts: 164

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

- Cheers -

Super User
Posts: 10,766

## 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: 66

## 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

Solution
‎10-26-2016 09:55 AM
Regular Contributor
Posts: 164

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

________________________

- Cheers -

☑ This topic is solved.