Finding the maximum date from 3 variables from 3 datasets

[elwayfan446]

Hey guys... my latest hurdle.

I have (so far) 3 data sets that have a variable called "updated_date".  This is the date the record for that dataset was last updated.  When joining each dataset by id, I need to be able to choose the maximum updated_date for a record depending on which dataset it is in.  Here is what I have so far:

proc sql;
select 
    id
    , <max updated date between d1.updated_date, d2.updated_date, d3.updated_date>
from dataset1 d1
inner join dataset2 d2 on d2.id = d1.id
inner join dataset3 d3 on d3.id = d1.id
;
quit;

I would prefer not using a case statement unless it is the only way because I will most likely be adding more tables that will also have their own updated_date.  After some research, I have seen some posts that talk about using arrays in a data step to do this, but I can't determine how that would be done across multiple data sets.  The post I am referring to is this one: https://communities.sas.com/t5/SAS-Programming/How-to-Get-Maximum-Row-s-Value-with-its-Name-of-Varia...

Any help would be greatly appreciated.

[Jagadishkatam]

proc sql;
select 
    id
    , max(d1.updated_date, d2.updated_date, d3.updated_date) as max_updated
from dataset1 d1
inner join dataset2 d2 on d2.id = d1.id
inner join dataset3 d3 on d3.id = d1.id
;
quit;

[Tom · Accepted solution]

SAS has a MAX(,) function that is different than the MAX() aggregate function of SQL.

proc sql;
select 
    id
    , max(d1.updated_date, d2.updated_date, d3.updated_date) as date format=date9.
from dataset1 d1
inner join dataset2 d2 on d2.id = d1.id
inner join dataset3 d3 on d3.id = d1.id
;

[elwayfan446]

Well, that was easy. I wasn't using MAX because I thought I was going to have to group by every other variable in my query (there will be a lot of them). I didn't realize there was a MAX function that wasn't an aggregate. Thanks so much!

[ballardw]

Another option could be the LARGEST function, which you specify which of the order (1= first or largest value, 2= 2nd largest and so on)

data example;
   input x1-x3;
datalines;
1 3 47
18 6 0
;

proc sql;
   select largest(1,x1,x2,x3) as maxvalue
   from example;
run;

Which may be helpful if you do use a group clause.