BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Zeus_Olympous
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

 

View solution in original post

6 REPLIES 6
Reeza
Super User

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
Obsidian | Level 7
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
Super User

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
Super User

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
Super User Tom
Super User

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
Obsidian | Level 7

Thanks a million, Tom!  Exactly what I wanted!

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3739 views
  • 2 likes
  • 4 in conversation