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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: