SAS - date column

[Aexor]

I am trying to make a macro which will give all the columns having date value in any format.

Any suggestions or help will be much appreciated. 

[Reeza]

data FormatInfo;
length Name $9. Type $8. Category $4. Desc $40. 
       DefW $5. MinW $5. MaxW $5. DefD $2. MinD $2. MaxD $2.; 

set sashelp.vcolumn;
where libname = 'SASHELP' and memname = 'CLASS';


Category = fmtinfo(Name, "Cat");  /* numeric, character, date, ... */
Type = fmtinfo(Name, "Type");     /* format, informat, or both */
Desc = fmtinfo(Name, "Desc");     /* short description of the format */
DefW = fmtinfo(Name, "DefW");     /* default width if you omit w. Example: BEST. */
MinW = fmtinfo(Name, "MinW");     /* minimum width */
MaxW = fmtinfo(Name, "MaxW");     /* maximum width */
DefD = fmtinfo(Name, "DefD");     /* default decimal digits */
MinD = fmtinfo(Name, "MinD");     /* minimum decimal digits */
MaxD = fmtinfo(Name, "MaxD");     /* maximum decimal digits */
;
 
proc print data=FormatInfo noobs;
   var Name Type Category Desc;
run;
 

Something like that can get you the categories which you can then filter.

To run it on your data set modify the WHERE statement to be the name of your data statements instead.

[Reeza]

This new version seems to work fine for me. Apparently you have to strip the numbers from the format. Look at the category variable to filter out all date/datetime variables. 

Do you need to factor in the usage of custom user defined formats? If your variables do not have date formats then detecting date variables is a much more difficult issue.

data demo;
first = '01Jan2019'd; format first date9.;
second = dhms(first, 2, 24, 44);
format second datetime22.;
run;


proc contents data=demo;
run;


data FormatInfo;
length format $9. Type $12. Category $10. Desc $40. 
       DefW $5. MinW $5. MaxW $5. DefD $2. MinD $2. MaxD $2.; 

set sashelp.vcolumn;
where libname = 'WORK' and memname = 'DEMO';

format = compress(format, , 'ka');

Category = fmtinfo(format, "Cat");  /* numeric, character, date, ... */
Type = fmtinfo(format, "Type");     /* format, informat, or both */
Desc = fmtinfo(format, "Desc");     /* short description of the format */
DefW = fmtinfo(format, "DefW");     /* default width if you omit w. Example: BEST. */
MinW = fmtinfo(format, "MinW");     /* minimum width */
MaxW = fmtinfo(format, "MaxW");     /* maximum width */
DefD = fmtinfo(format, "DefD");     /* default decimal digits */
MinD = fmtinfo(format, "MinD");     /* minimum decimal digits */
MaxD = fmtinfo(format, "MaxD");     /* maximum decimal digits */
;
 
proc print data=FormatInfo noobs;
   var Name Format Type Category Desc;
run;


data one;   
   /*These do not work*/                                                                                                                            
   a=fmtinfo('date9','cat');
   b=fmtinfo('date9','type');
   c=fmtinfo('date9','desc');
   output;
   a=fmtinfo('datetime22','cat');
   b=fmtinfo('datetime22','type');
   c=fmtinfo('datetime22','desc');
   output;
   
   /*These do work*/
   a=fmtinfo('date','cat');
   b=fmtinfo('date','type');
   c=fmtinfo('date','desc');
   output;
   a=fmtinfo('datetime','cat');
   b=fmtinfo('datetime','type');
   c=fmtinfo('datetime','desc');
   output;

run; 

[smantha]

data FormatInfo;
length dataf $100.;
set sashelp.vcolumn;
where libname = 'SASHELP' and memtype='DATA';
dataf = coalescec(informat,format);
if (index(dataf,'YY') >0 or index(dataf,'MM')>0 or index(dataf,'DD')>0 or index(dataf,'DAT')>0 or index(dataf,'JUL')>0)
 and index(dataf,'COMMA') =0;

run;

[Aexor]

Hi Smantha,
Thanks for your response. i just wanted to comfirm What if we have check for All 12 month like "JUL" . Do we need to follow the same steps ?

[andreas_lds]

"JUL" is not related to the month July, but to the formats for Julian-calendars, like JULDAYw.