topic Re: Identifying multiple date variables in SAS Procedures

Identifying multiple date variables

Sivaram97 — Thu, 27 Jun 2019 19:19:34 GMT

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..?

Re: Identifying multiple date variables

PeterClemmensen — Thu, 27 Jun 2019 19:23:40 GMT

Hi and welcome to the SAS Community 🙂

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

Re: Identifying multiple date variables

Sivaram97 — Thu, 27 Jun 2019 19:27:29 GMT

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

Re: Identifying multiple date variables

Tom — Thu, 27 Jun 2019 19:43:22 GMT

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

Re: Identifying multiple date variables

PaigeMiller — Thu, 27 Jun 2019 19:47:01 GMT

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.

Re: Identifying multiple date variables

Sivaram97 — Thu, 27 Jun 2019 19:57:13 GMT

Thank you @Tom it'll helps a lot

Re: Identifying multiple date variables

ballardw — Thu, 27 Jun 2019 20:48:37 GMT

@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;

Re: Identifying multiple date variables

Sivaram97 — Thu, 27 Jun 2019 21:14:10 GMT

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....