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 -
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.