Find the min and max date values across multiple date variables using Array

[sasheadache]

Hi All, I have a dataset which contains a list of date variables (in columns) and I need to return the min() and max() values for each one.

I have used the below code to get all date variables from my source table, to then pass them into a macro variable to ensure the field list stays dynamic.

/* OBTAIN FIELD LIST */
proc contents data=test out=field_list; run;

/* PASS DATE FORMATTED COLUMNS INTO MACRO TOKEN */
PROC SQL;
SELECT NAME INTO:DATE_FIELDS separated by ' ' FROM field_list WHERE FORMAT = "DATE"; 
QUIT;

I was then hoping to use the &field_list in an array find the min and max value for each, can anyone assist please?

Desired Array Output:

	MIN	MAX
Var1	01-Jan-00	30-Jan-24
Var2	etc.	etc.
Var3	etc.	etc.
Var4	etc.	etc.
Var5	etc.	etc.

[Tom]

Isn't that what PROC MEANS is for?

proc means min max data=test;
  var &date_fields;
run;

PS you can get a better list of variables if you use FMTINFO() function.  Then you can find the variables that are using YYMMDD and other date type formats.

SELECT NAME
  INTO :DATE_FIELDS separated by ' ' 
  FROM field_list 
  WHERE 'date' = fmtinfo(format,'cat')
  order by varnum
;

[Kurt_Bremser]

Query the DICTIONARY table, and run PROC MEANS.

proc sql;
select name into :date_fields separated by " "
from dictionary.columns
where libname = "WORK" and memname = "TEST" and format like 'DATE%',
quit;

proc means data=test min max;
var &date_fields.;
output out=want / autoname;
run;

[sasheadache]

The 'Results' is exactly the view I wanted to see (I just need to re-format the mix/max columns to show as a date).

The 'Output data' however has transposed the data in the opposite way i.e. variables are in columns and min max in rows. I ideally wanted output table to match the 'Results' output.... do you know if there is a simple way to force the output to show the same view? Or will I need to do some further steps to recreate this view i.e.

1. drop the _TYPE_ _FREQ_ columns

2. retain where _STAT_   in ('MIN ' MAX')

3. transpose the data

Thanks

[Reeza]

Look into the STACKODSOUTPUT option to get results more similar to the displayed output.

[Tom · Accepted solution]

Just transpose the normal output of PROC MEANS/SUMMARY.

Add a FORMAT statement to attach whatever type of date display you want.

proc summary data=test;
  var &date_fields;
  output out=stats;
run;
proc transpose data=stats out=want;
  where _stat_ in ('MIN' 'MAX');
  id _stat_;
  var &date_fields;
  format &date_fields yymmdd10.;
run;

[sasheadache]

Thanks! On the off chance, do you know if there is a way to use datepart() in either the proc summary/means or transpose?

Typically I have a couple of variables which contain datetime and wanted to save another processing step if I could. (I tried setting additional format lines for the impacted fields to the proc transpose but that just overrides everything.)

Appreciate the support!

[Tom]

If you are willing to accept the normal standard output of PROC SUMMARY then you won't have any trouble. Example:

data test;
  now=datetime();
  today=date();
  format now datetime19. today date9.;
run;

proc summary data=test;
  var now today;
  output out=stats;
run;

proc print data=stats;
  where _stat_ in ('MIN','MAX');
run;

But if you transpose them you will need to add a step somewhere to convert them.  Perhaps by converting the DATETIME values into DATE values?  It might be faster to do it after creating the transposed dataset.

proc transpose data=stats out=want;
  where _stat_ in ('MIN','MAX');
  id _stat_;
  var now today;
run;

data want;
  set want;
  if _name_ in ('now') then do;
   min=datepart(min);
   max=datepart(max);
  end;
  format min max date9.;
run;

[Tom]

You can get the list of both date and datetime variables.  

proc sql;
SELECT case when 'date' = fmtinfo(format,'cat') then name else ' ' end
     , case when 'datetime' = fmtinfo(format,'cat') then name else ' ' end
  INTO :DATE_FIELDS separated by ' ' 
     , :DATETIME_FIELDS separated by ' '
  FROM field_list 
  WHERE fmtinfo(format,'cat') in ('date','datetime')
  order by varnum
;

Then use both lists in the PROC SUMMARY step and only the DATETIME list in the DATEPART() step.

proc summary ....
  var &date_fields &datetime_fields;
...

...
  if indexw("&datetime_fields",trim(_name_),' ') then do;
....

[sasheadache]

Thanks Tom! Really appreciate your time