Change DATE formats to DDMMYYS10. for ALL (unknown number) date variables in a data set/LIBRARY

[Zeus_Olympous]

Dear all,

I have either a data set or many datasets in a LIBRARY that contain unknown number of date variables in possibly different date formats.

I would like to CHANGE the various date formats that belong to unknown number of date variables in my dataset or in my library to DDMMYYS10. (same for ALL date variables).

Is there any function in SAS that I can apply it to make It happen.

Thank you in advance for your guidance.

[Reeza]

Can you identify the date variables in any way? Do they have a consistent naming structure or some other way they can be identified?

Otherwise, the answer is no. The reason is SAS stores dates as numbers, so telling the difference between an actual number vs a date is not easy. Search on here for this question, it's been  asked and answered a few times and has a lengthy discussion that would be helpful as background. 

[Zeus_Olympous]

Thanks Reeza for your hint. No they have no standard naming and they have different date formats DATE, DDMMYY etc. If I could isolate ALL date variables then I could change all their different formats to DDMMYYS10. .. There must be a way. Thanks again.

[Reeza]

Like I mentioned, if you can assume something you can sort of do this, but you cannot GUARANTEE that it's 100% correct. 

Under the assumption that the variable will already have a Date/Datetime format there are some solutions on StackOverflow.

https://stackoverflow.com/questions/38470459/sas-programatically-identifying-numeric-variables-that-...

[ballardw]

About the only way to be reasonably sure that a variable is date-valued is to check a format. I might start with this code:

proc sql;

   select distinct format

   from dictionary.columns

   where libname='YOURLIB';

quit;

This will find all assigned formats. Identify those that are date formats. NOTE: Make sure they are not datetime formats.

You can then use that information to further select

proc sql;

    create table datefmts as

    select memname, name

    from dictionary.columns

    where libname='YOURLIB'

               and upcase( format) in ('DATE7.' ,'MMDDYY10.')

    ;

quit;

The list of date formats comes from the previous proc sql step.

You can then use the resulting the datefmts data set to call proc datasets and modify the data set variable formats using Proc Datasets.

Note that any variables that just happen to contain things that look like dates, such as character variables of '01/25/2015' or numerics line 20170512 will not have a chance of being identified with formats. You might be able to find common date related variable name parts in the Dictionary.columns data set such as "StartDate" "End_Date" "Purchase_date" but if they don't have an actual date format associated then you'll need to create a SAS date valued variable and apply the format there.

[Tom · Accepted solution]

With the SAS 9.4 TS1M3 release there is a new FMTINFO() function that should help with this.

So just use PROC CONTENTS to get the list of variables in the library or dataset. Note that for this job PROC CONTENTS works much better than SASHELP.VCOLUMN since the FORMAT variable has the bare format name without the width and decimal settings.

proc contents data=mylib._all_ noprint out=contents;
run;

Then you can use a data step like this to generate PROC DATASET code to change the formats attached to the date variables.

data _null_;
  set contents;
  where fmtinfo(format,'cat')='date';
  by libname memname ;
  if first.libname then call execute(catx(' ','proc datasets nolist lib=',libname,';')) ;
  if first.memname then call execute(catx(' ','modify',memname,';format',name)) ;
  else call execute(' '||trim(name)) ;
  if last.memname then call execute(' DDMMYYS10.; run;') ;
  if last.libname then call execute('quit;') ;
run;

Personally I would use a format like DATE or YYMMDD instead to avoid the confusion between Dec 10th and Oct 12th  that can happen if you use either MMDDYY or DDMMYY.

[Zeus_Olympous]

Thanks a million, Tom!  Exactly what I wanted!