How to take Max date

[sasuser123123]

Hello!

So I've data which contains of.

Data new;
Input subj test $ result Date
Data lines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;
so I need the New variable "NEW" that contains only latest date of 'SYS' test only. Could you please tell me how to get that variable.


Thank you!

[novinosrin · Accepted solution]

Do you mean this?

Data new;
Input subj test $ result Date :yymmdd10.;
format date yymmdd10.;
lines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;
proc sql;
create table want as
select *, max((test='SYS')*date) as max format=yymmdd10.
from new
group by subj;
quit;

[Amir]

As @novinosrin asked not sure if you want the whole column set to the max date for "SYS", or if you just want a macro variable with the value.

The following creates a new column using a data step alternative:

data have;
   input subj
         test   $
         result
         date   yymmdd10.
   ;

   format date date9.;

   datalines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;

/* use double DOW loop */
data want;
   do until (last_check);
      set have end = last_check;
      if test eq 'SYS' then
         new = max(new,date);
   end;

   format new date9.;

   do until (last_obs);
      set have end = last_obs;
      output;
   end;
run;

Amir.

[sasuser123123]

Hello @novinosrin !
I run the program which you gave. So the new column max contains the maximum date for all tests but I need only for test 'SYS' .
How do that one.

[Amir]

What value should be in the new column for the rows that do not have a test value of 'SYS'?

Should they be blank or have the same value as the date column or something else?

Amir.

[novinosrin]

Good morning @sasuser123123  A simple tweak 

Data new;
Input subj test $ result Date :yymmdd10.;
format date yymmdd10.;
lines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;
proc sql;
create table want as
select *, ifn(test='SYS',max((test='SYS')*date),.) as max format=yymmdd10.
from new
group by subj;
quit;

[sasuser123123]

Thank you for your quick response!!

Yeah, they should be blank (The rows do not have test 'SYS')

[Amir]

I should have said missing, rather than blank.

The following code sets the value to missing, which for numeric values is '.' by default, but if you really want a blank then you can comment out the options statement at the top to change the default from '.' to '', but the value will still be considered as missing.

/* change default numeric missing from '.' to '' */
*options missing = '';

/* use double DOW loop */
data want(drop = saved_new);
   do until (last_check);
      set have end = last_check;
      if test eq 'SYS' then
         new = max(new,date);
   end;

   format new date9.;
   saved_new = new;

   do until (last_obs);
      set have end = last_obs;

      if test ne 'SYS' then
         call missing(new);
      else
         new = saved_new;

      output;
   end;
run;

Amir.

[sasuser123123]

Thank you so much for your assistance