SAS - Change date format returned from database

Question

I'm pulling data from many Teradata tables that have dates stored in MM/DD/YYYY format (ex: 8/21/2003, 10/7/2013). SAS returns them as DDMMMYYYY, or DATE9 format (ex: 21AUG2003, 07OCT2013). Is there a way to force SAS to return date variables as MM/DD/YYYY, or MMDDYY10 format? I know I can manually specify this for specific columns, but I have a macro set up to execute the same query for 65 different tables:

%macro query(x);

proc sql;
    connect using dbase;
    create table &x. as select * from connection to dbase
        (select *
        from table.&x.);
    disconnect from dbase;
quit;


%mend(query);
%query(bankaccount);
%query(budgetcat);
%query(timeattendance);

Some of these tables will have date variables and some won't. So I'd like the value to be returned as MMDDYY10 format by default. Thanks for your help!

No, you cannot do this within PROC SQL easily. You can do it after the fact pretty easily. Your example code is just copying tables over, so if you can set up a libname and use a data step you have some more options to do that. SAS 9.4 did add a way to automatically find all date variables so you can change the formats more easily. — Reeza
Maybe using format inside the SELECT clause? Not sure if ODBC allows it but proc sql on SAS data does. This requires naming columns and not using asterisk: select mydate format=MMDDYY10., ... from ... — Parfait
As @reeza, it is dififuclt through explicit pass through. As whatever format you try in Teradata and when it is moved back to SAS it will be usually date9. you can create table with 0 records and then use proc datasets and change format and then do an insert. Please try create table &x. (sasdatefmt=(datecolumn='MMDDYY10.')), if you already known your date column. — Kiran
@Reeza Are you referring to FMTINFO? It looks like I could store my datasets in a library then use that function to change the date format of all of them. — natnay
Yes, you can use FMTINFO to determine which variables are date using the CAT option. — Reeza

natnay natnay · Accepted Answer · 2018-11-01T17:03:42

Per the comments to my question, I was able to figure this out using the FMTINFO function. I pretty much used this same code:

proc contents data=mylib._all_ noprint out=contents;
run;

data _null_;
  set contents;
  where fmtinfo(format,'cat')='date';
  by libname memname ;
  if first.libname then call execute(catx(' ','proc datasets nolist lib=',libname,';')) ;
  if first.memname then call execute(catx(' ','modify',memname,';format',name)) ;
  else call execute(' '||trim(name)) ;
  if last.memname then call execute(' DDMMYYS10.; run;') ;
  if last.libname then call execute('quit;') ;
run;

Found here: https://communities.sas.com/t5/SAS-Procedures/Change-DATE-formats-to-DDMMYYS10-for-ALL-unknown-number-date/td-p/366637

SAS - Change date format returned from database

1 Answers