BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
atul_desh
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Oligolas
Barite | Level 11

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 -

View solution in original post

6 REPLIES 6
Oligolas
Barite | Level 11

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 -

atul_desh
Quartz | Level 8

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

Oligolas
Barite | Level 11

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 -

Ksharp
Super User
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;
atul_desh
Quartz | Level 8

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 🙂

 

Oligolas
Barite | Level 11

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 -

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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