Format ALL dates from date9 to mmddyy10

[DPorcelan]

I am a very experienced programmer, but a novice in SAS.

I would like to convert ALL date9 fields to yymmdd10.

I am unloading an entire DB2 table with Proc SQL Select *.

I do not know what the fields are, nor do I want to.

In the case of the first DB2 table, there were 59 date fields.

My program is intended to be very generic, in so that the Table is passed in as an input parameter.  Thus not wanting to code any field names.

I would like all dates to be in the same format as on the database (yyyy-mm-dd).

Please advise on how this could be done.

Thanks

Here is my sample code:

%MACRO EXTRACT(SSID=,CREATOR=,TAB=,DELIM=);
* EXTRACT DATA FROM ANY TABLE BASED ON TAB_KEY
OPTIONS
MISSING=0
MPRINT
ERRORS=0
NOOVP
;
 PROC SQL;
 %include "/sas/connect/&SSID.connect.sas";
 CREATE TABLE TABLE1 AS
 SELECT *
 FROM CONNECTION TO DB2(
 SELECT *
 FROM &CREATOR..&TAB
 WHERE TAB_KEY IN (
'12340AABB',
'23450CCDD',
'34560DDEE',
'45670RRFF',
'xxxxxxxxx')
           );
 DISCONNECT FROM DB2;
 %PUT &SQLXMSG;
 proc export
 data=table1
 outfile=ddout
 dbms=dlm  ;
 delimiter=&DELIM;

RUN;
%MEND EXTRACT;
%EXTRACT(CREATOR=AAA,DELIM=',',TAB=AA11,SSID=sys1)

[DavePrinsloo]

Here is a macro that uses the %ut_varlist macro documented in https://communities.sas.com/t5/SAS-Programming/Macro-function-to-return-a-list-of-variables-in-a-tab...

(Download the attachment, add them to your sasautos macro library (or %include).   

See example 10 in the above posting....

%macro set_data_fmts(table=);
%local datecols datetimecols lib memname;
%let datecols=%ut_varlist(table=&table., type=DATE);
%let datetimecols=%ut_varlist(table=&table., type=DATETIME);

%let lib=%scan(&table.,-2);
%if &lib.=%str() %then %let lib=work;
%let memname=%scan(&table.,-1);

proc datasets library=&lib. nolist;
modify &memname.;
format &datecols. yymmdd10.;
format &datetimecols. E8601DN10.;
quit;
%mend set_data_fmts;

The above macro just changes formats in the column metadata.     However, since DB2 stores all dates as datetimes,   you may need to convert

dates into datetimes using something like:

/* convert all dates into timestamps */
data &table.;
set &table.;
array datecols &datecols;
do _i_=1 to dim(datecols);
    datecols(_i_) =dhms(datecols (_i_),0,0,0);
end;
run;

[s_lassen]

A correction: DB2 does not store all dates as datetimes, according to the SAS/Access documentation for DB2, there can be both DATE (shown in SAS with DATE9. format) and DATETIME values (shown in SAS with DATETIME30.6 format). I seem to remember a time when there were only datetimes on DB2, but that is long gone now.

So if @DPorcelan only wants the actual dates converted, there is no need to convert the data, just change the formats.

@DPorcelan : You do not necessarily need a macro to get the fields with format DATE9., you can also use just SQL, e.g.:

proc sql noprint;
  select name into :date_cols separated by ' ' 
  from dictionary.columns
  where libname='WORK' and memname='TABLE1' and format='DATE9.';
quit;

which you can then use in PROC DATASETS

proc datasets library=work nolist;
modify table1;
format &date_cols. yymmdd10.;
quit;

and then proceed to PROC EXPORT. But the utility macro may be good for other stuff as well. I have shown the code for WORK.TABLE1, as that was the name of the SAS output in your example.

[Tom]

I don't think there is any SAS/Access engine option or dataset option that let's you control the format assigned to date values.  But it is not hard to convert them.  Just get the metadata about the variables in your dataset(s) and use it to generate PROC DATASETS statements to change the format attached to any variable that has a date type format attached. The 'CAT' option of the FTMINFO() function will let you find the variables that have a date category format attached. 

It is easier to use FMTINFO() with the output of PROC CONTENTS since the format name, length and decimal places are in separate variables instead of mushed into one.

proc contents data=work._all_ noprint out=contents ;
run;
data _null_;
  set contents ;
  by libname memname ;
  where fmtinfo(format,'cat')='date';
  if first.libname then call execute(catx(' ','proc datasets nolist lib=',libname,';'));
  if first.memname then call execute(catx(' ','modify',nliteral(memname),';'));
  call execute(catx(' ','format',nliteral(name),'yymmdd10.;'));
  if last.memname then call execute('run;');
  if last.libname then call execute('quit;');
run;

But you could do it using DICTIONARY.COLUMNS (or the SASHELP.VCOLUMN view to it) with a little help from a regular expression to remove the extra bits from the format specification to get down to just the name of the format.

  set sashelp.vcolumn;
  by libname memname ;
  where libname='WORK';
  where also fmtinfo(prxchange('s/\d*\.\d*$//',1,trim(format)),'cat')='date';

Example Data:

data test1;
  format date1 mmddyy10. date2 ddmmyy10. date3 yymmdd10. date4 date9.;
  date1=today();
  date2=date1;
  date3=date1;
  date4=date1;
  x=1;
  c='A';
run;

Log

NOTE: CALL EXECUTE generated line.
1   + proc datasets nolist lib= WORK ;
2   + modify TEST1 ;
3   + format date1 yymmdd10.;
4   + format date2 yymmdd10.;
5   + format date3 yymmdd10.;
6   + format date4 yymmdd10.;
7   + run;

NOTE: MODIFY was successful for WORK.TEST1.DATA.
8   + quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Outputs

BEFORE
Obs         date1         date2         date3        date4    x    c

 1     05/30/2020    30/05/2020    2020-05-30    30MAY2020    1    A

AFTER
Obs         date1         date2         date3         date4    x    c

 1     2020-05-30    2020-05-30    2020-05-30    2020-05-30    1    A

[Patrick]

@DPorcelan 

You've got already solutions how to dynamically generate the required Proc Datasets for changing the formats. 

One thing to consider: IF you also plan at any stage to load data from SAS back to DB2 then the default formats applied when unloading are what you need when uploading. This is because SAS doesn't have a data type of Date so the formats are used by SAS to determine that it's dealing with a SAS Date or Datetime value. ...and I don't know why but this only works implicitly if you use the default formats (even though SAS should imho also recognize the other out-of-the-box formats - but it doesn't).

And unrelated to your question:

- You might want to have a look into the READBUFF option. The default value is often too low.

- I assume you've got the connect to statement in there. Ideally you have an authentication domain created in SAS metadata and don't use user and password. And you then write your connect statement directly in the code using the AUTHDOMAIN= option. If you want to stick with something in an external file then I personally prefer to not use an %include with a fully qualified pathname but to implement via an autocall macro - one of the default folders SAS searches for such macro code is under .../SASEnvironment/SASMacro (the search path is defined and extendable via option SASAUTOS).

%include "/sas/connect/&SSID.connect.sas";