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.
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
;
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;
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
;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.