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.
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.
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;
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;
"JUL" is not related to the month July, but to the formats for Julian-calendars, like JULDAYw.
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!
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.