I want Max mum date value per ID from 2 data sets from different variables.
Post test data in the form of a datastep. At a guess something like:
data want; set have; array date{3}; mdate=max(of date{*}); run; proc sort data=want nodupkey; by id descending mdate; run;
This is why it is a good idea to follow the guidance for posting questions which you will see when you post. Provide clear test data (in the form of a datasteps) which show you problem, and full information about what needs to happen plus test output.
Why is there are requirement for SQL?
Your main problem here is that your data is transposed - this means the data goes across the table. SQL is built specifically to work with normalised data, i.e. data going down the table. So, step one will be getting the data into a way that SQL can work with, if you have 3 dates then you could do:
select SUBJECT_ID,DATE1 as DATE from HAVE union all select SUBJECT_ID,DATE2 as DATE from HAVE union all select SUBJECT_ID,DATE3 as DATE from HAVE
From this, you can put it in a sub-query, then do max on that data:
proc sql; create table WANT as select distinct SUBJECT_ID, DATE from (
select SUBJECT_ID,DATE1 as DATE from HAVE
union all
select SUBJECT_ID,DATE2 as DATE from HAVE
union all
select SUBJECT_ID,DATE3 as DATE from HAVE ) group by SUBJECT_ID; quit;
Hi.
I think you could consolidate all data in one table, and get the max date by ID from there, like this:
Hope it helps.
proc sql noprint;
create table data3 as
select ID, max(date) as maxdate format=yymmdd10. from
(select ID, date1 as date from data1 union
select ID, date2 as date from date2 union
select ID, date3 as date from data2)
group by ID;
quit;
Hope it helps.
Daniel Santos @ www.cgd.pt
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.