BookmarkSubscribeRSS Feed
SrikanthY
Calcite | Level 5

I want Max mum date value per ID from 2 data sets from different variables.


Max date.PNG
5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
SrikanthY
Calcite | Level 5
there are 2 separate data sets first wee need to set or merge. and I want
it in sql

##- Please type your reply above this line. Simple formatting, no
attachments. -##
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

 

DanielSantos
Barite | Level 11

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

SrikanthY
Calcite | Level 5
Thank you for your quick response. I got the exact o/p what I have expected.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 981 views
  • 0 likes
  • 3 in conversation