topic Finding the maximum date from 3 variables from 3 datasets in SAS Programming

Finding the maximum date from 3 variables from 3 datasets

elwayfan446 — Fri, 17 Apr 2020 17:07:25 GMT

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-Variable-into/td-p/326629

Any help would be greatly appreciated.

Re: Finding the maximum date from 3 variables from 3 datasets

Jagadishkatam — Fri, 17 Apr 2020 17:22:10 GMT

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;

Re: Finding the maximum date from 3 variables from 3 datasets

Tom — Fri, 17 Apr 2020 17:24:00 GMT

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
;

Re: Finding the maximum date from 3 variables from 3 datasets

elwayfan446 — Fri, 17 Apr 2020 17:32:30 GMT

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!

Re: Finding the maximum date from 3 variables from 3 datasets

ballardw — Fri, 17 Apr 2020 17:37:05 GMT

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.