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

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

Zeus_Olympous — Tue, 13 Jun 2017 15:27:13 GMT

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.

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

Reeza — Tue, 13 Jun 2017 15:29:57 GMT

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.

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

Zeus_Olympous — Tue, 13 Jun 2017 15:54:48 GMT

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.

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

ballardw — Tue, 13 Jun 2017 15:57:28 GMT

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.

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

Reeza — Tue, 13 Jun 2017 16:09:05 GMT

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-represent-date-time-dat

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

Tom — Wed, 14 Jun 2017 04:59:04 GMT

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.

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

Zeus_Olympous — Wed, 14 Jun 2017 09:03:23 GMT

Thanks a million, Tom! Exactly what I wanted!