topic Re: SAS - date column in SAS Programming

SAS - date column

Aexor — Thu, 30 Jul 2020 15:31:13 GMT

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.

Re: SAS - date column

Reeza — Thu, 30 Jul 2020 15:40:12 GMT

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.

Re: SAS - date column

smantha — Thu, 30 Jul 2020 18:13:16 GMT

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;

Re: SAS - date column

Aexor — Fri, 31 Jul 2020 12:00:27 GMT

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 ?

Re: SAS - date column

Reeza — Tue, 04 Aug 2020 15:10:24 GMT

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;

Re: SAS - date column

andreas_lds — Wed, 05 Aug 2020 04:30:38 GMT

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