How do I select multiple observations for the ‘max’ value of a column using Proc Sql?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

How do I select multiple observations for the ‘max’ value of a column using Proc Sql?

I have spent too much time trying to resolve this so I am reaching out for some assistance.....Any help is appreciated!  

 

I want to select the row that has the ‘max’ value of ‘TYear’ from the following example of my input table and column names.

I have included the sql statement I am using. It is selecting all the observations for group.

 

Table: MAX_STORE_DATA

 

Column names:

Region     District   StoreNbr   TYear

1          123        54         2012      

1          123        54         2013

1          123        54         2014      

1          456        54         2011      

1          456        54         2012      

1          456        54         2013      

1          789        54         2010      

1          789        54         2011      

1          789        54         2012 

 

I want to achieve the results that would look like this:

 

1          123        54         2014      

1          456        54         2013      

1          789        54         2012      

 

     

create table work.MAX_STORE_DATA as
    select *
        from (select *
              from STORE_DATA t1
              group by t1.region, t1.district, t1.StoreNbr, t1.TYear)
    having t1.TYear=max(t1.TYear)  ;

 

 


Accepted Solutions
Solution
‎02-05-2016 10:23 AM
Trusted Advisor
Posts: 1,114

Re: How do I select multiple observations for the ‘max’ value of a column using Proc Sql?

Hi @ncsthbell,

 

you can simplify your PROC SQL step as follows:

proc sql;
create table work.MAX_STORE_DATA as
select *
from STORE_DATA
group by region, district, StoreNbr
having TYear=max(TYear);
quit;

View solution in original post


All Replies
Solution
‎02-05-2016 10:23 AM
Trusted Advisor
Posts: 1,114

Re: How do I select multiple observations for the ‘max’ value of a column using Proc Sql?

Hi @ncsthbell,

 

you can simplify your PROC SQL step as follows:

proc sql;
create table work.MAX_STORE_DATA as
select *
from STORE_DATA
group by region, district, StoreNbr
having TYear=max(TYear);
quit;
Frequent Contributor
Posts: 82

Re: How do I select multiple observations for the ‘max’ value of a column using Proc Sql?

Thank you so much  . Your solution gave me the exact results I was looking for.   I always tend to over complicate things Smiley Sad 

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: How do I select multiple observations for the ‘max’ value of a column using Proc Sql?

As an alternative, you could do - useful in different situations:

data want;
  set have;
  by region district;
  if last.district;
run;

This assumes the data is sorted, 

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 319 views
  • 2 likes
  • 3 in conversation