BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ProcWes
Quartz | Level 8

I am trying to identify all date fields in my data.  It is a challenge because SAS stores them as number, so if I do something like _numeric_, I get both the numbers and the dates.  If they were all the same format, contents would work to find them, but there are many acceptable date formats.

 

Here's an example.  How could I catch both DATE which is in the sashelp data and FAKE which I'm creating in a different format?

 

data test;
set sashelp.stocks;
format xx mmddyy10.;
fake = today();
run;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Use the FMTINFO() function to find the variables that date category formats attached to them.

Use the output of PROC CONTENTS to get the FORMAT name without the width/decimal specication.

proc contents data=HAVE out=contents noprint; run;
proc sql noprint;
select name into :datevar separated by ' '
  from contents
  where 'date'=fmtinfo(format,'cat')
;
%let ndates=&sqlobs;
quit;

View solution in original post

6 REPLIES 6
Shmuel
Garnet | Level 18

I can think of several ways to identify or guess which fields are dates:

1) As you noticed all dates are NUM type but can be string too (like "20210317"  todays date);

2) For NUM type check FORMAT or INFORMAT compared to a long list of possibilities.

3) If no FORMAT nor INFORMAT assigned then guessing is the only possibility:

     If you know your data then non missing INTEGERs may be dates if they are in logical range.

     It needs checking all the variables' values in all observations of the data set.

image.png

  Note: dtn = input(dtx,date9.);  

4) Check variable names according to expected language, file was created by.

     For English - most names will include 'date',  'dt', 'year', 'yy', 'month', 'mon', 'mm' etc..

     Human eyes scanning  of the variable names are faster then any programming.

5) Check variable labels if exist or any other Metadate or documentation you might have.

6) If you have already programs using this dataset - check how the program relates to the data:

     which functions, formats or informats are used with each guessed variable.

ballardw
Super User

If you do not attach a standard date informat or format to the variable, or name it with the word "date" somewhere in the name or have the word "date" in a label there is no way to "identify all".

 

And if you have character values that look like dates then even more problematic.

 

You can search variable metadata for the characteristics above in the SASHELP.Volumns view (or dictionary.columns if using Proc SQL).

 

If you want "guesses" you could look for numeric variables whose values are integer and in an expected range of values that might be dates but I would be very skeptical about the results in general.

Tom
Super User Tom
Super User

Use the FMTINFO() function to find the variables that date category formats attached to them.

Use the output of PROC CONTENTS to get the FORMAT name without the width/decimal specication.

proc contents data=HAVE out=contents noprint; run;
proc sql noprint;
select name into :datevar separated by ' '
  from contents
  where 'date'=fmtinfo(format,'cat')
;
%let ndates=&sqlobs;
quit;
ProcWes
Quartz | Level 8
It looks like this will work, thank you!
ishmo65
Obsidian | Level 7

@Tom syntax error in the code - should be "PROC CONTENTS ..."

 

proc contents data=HAVE out=contents noprint; run;
proc sql noprint;
select name into :datevar separated by ' '
  from contents
  where 'date'=fmtinfo(format,'cat')
;
%let ndates=&sqlobs;
quit;
Tom
Super User Tom
Super User

Yes.  Thanks.  Fixed it now.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 4382 views
  • 7 likes
  • 5 in conversation