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;
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 -
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 -
thanks but what if highest 'amount' was there two times then counter will give wrong result.
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 -
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;
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 🙂
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 -
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.