Identifying multiple date variables

[Sivaram97]

Hi sas community,
I'm trying to find out that the count of all date variables in all datasets from a library. Is there any specific function or anything there for identifying date variables..?

[PeterClemmensen]

Hi and welcome to the SAS Community 🙂

Can you be a bit more specific? What do you mean by "count of all date variables"?

[Sivaram97]

It means that I want to know how many date variables are present in each
sas dataset in a library

[ballardw]

---

@Sivaram97 wrote:
It means that I want to know how many date variables are present in each
sas dataset in a library

---

Have all of your "date" variables created as SAS date valued variables? Do all of them have appropriate date related formats?

Do they have labels assigned that have the word date in them, or do the variables have date as part of their name?

Then this may be possible by examining the name, format and/or label fields of the data sets.

However if you have character variables, or numeric variables that only look like a date such as 20190525 but is a simple numeric  without labels or names with "date" then those may be harder to find.

An example that may get you started: Replace LIBRARY  with your library name in upper case as that is how they are stored in the meta data. I am also searching for a couple of the date formats but there are so many I only show a couple.

proc sql;
   create table datevariables as
   select libname , memname, name,  label, format
   from dictionary.columns 
   where libname='LIBRARY' and 
         (
         (index(upcase(name),'DATE')>0)  or
         (index(upcase(label),'DATE')>0) or
         ( index(format, 'MMDDYY')>0) or
         ( index(format, 'DATE')>0)
         
        )
   ;
quit;

[Tom · Accepted solution]

Use the FMTINFO() function to test if the variable has a format of DATE type associated with it.  

Do you want just DATE values or do you also want to look for DATETIME and TIME values?

data test;
  input id date time datetime amt comment $20.;
  informat date date. time time. datetime datetime. ;
  format date yymmdd10. time hhmm. datetime dtdate9.;
  format amt dollar10.2 ;
cards;
1 01jan2019 12:30 01JAN2019:12:30 1234.56 Example value
;

proc contents data=work._all_ out=cont noprint;
run;

data check;
 set cont;
 fmt_cat = fmtinfo(format,'cat');
run;

proc freq;
 where memname='TEST';
 tables fmt_cat;
run;

The FREQ Procedure

                                          Cumulative    Cumulative
fmt_cat          Frequency     Percent     Frequency      Percent
------------------------------------------------------------------
FMTNAME BLANK           2       33.33             2        33.33
curr                    1       16.67             3        50.00
date                    1       16.67             4        66.67
datetime                1       16.67             5        83.33
time                    1       16.67             6       100.00

[PaigeMiller]

The answer by @Tom is a good one for variables that have been assigned a date or datetime or time format. Otherwise, it fails. You need to examine the data (and know what each field represents) in that case, I think there's a maxim about this.

[Sivaram97]

Thank you @Tom it'll helps a lot

[Sivaram97]

It is also useful @ballardw. But if I've date variable with the name created or modified or the formats also have a lot or the variables which don't have labels then everytime I need to check the data and create index functions....