topic Re: Max value from all variables by subject ID in SAS Programming

Max value from all variables by subject ID

SrikanthY — Thu, 19 Jan 2017 14:52:58 GMT

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

Re: Max value from all variables by subject ID

RW9 — Thu, 19 Jan 2017 15:07:28 GMT

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;

Re: Max value from all variables by subject ID

SrikanthY — Thu, 19 Jan 2017 15:12:04 GMT

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. -##

Re: Max value from all variables by subject ID

DanielSantos — Thu, 19 Jan 2017 15:14:08 GMT

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

Re: Max value from all variables by subject ID

SrikanthY — Thu, 19 Jan 2017 15:21:08 GMT

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. -##

Re: Max value from all variables by subject ID

RW9 — Thu, 19 Jan 2017 15:23:13 GMT

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;