DATA Step, Macro, Functions and more

Max value from all variables by subject ID

Reply
Contributor
Posts: 24

Max value from all variables by subject ID

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


Max date.PNG
Super User
Super User
Posts: 7,997

Re: Max value from all variables by subject ID

Posted in reply to SrikanthY

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;
Contributor
Posts: 24

Re: Max value from all variables by subject ID

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. -##
Super User
Super User
Posts: 7,997

Re: Max value from all variables by subject ID

Posted in reply to SrikanthY

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;

 

 

Super Contributor
Posts: 474

Re: Max value from all variables by subject ID

[ Edited ]
Posted in reply to SrikanthY

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

Contributor
Posts: 24

Re: Max value from all variables by subject ID

Posted in reply to DanielSantos
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. -##
Ask a Question
Discussion stats
  • 5 replies
  • 134 views
  • 0 likes
  • 3 in conversation