topic Re: How do I get ODS Excel to output a date field? in ODS and Base Reporting

How do I get ODS Excel to output a date field?

tomcmacdonald — Wed, 20 Dec 2017 20:47:18 GMT

SAS code looks like this:

data foo;
    input id a date10.;
    datalines;
1 '1Jan2017'd
2 '2Jan2017'd
3 '1Jan2006'd
4 '31Dec2006'd
;
run;

ods excel file=&file;

proc print data=foo noobs;
  var a / style(data)={tagattr='type:DateTime'};
run;

ods excel close;

In the Excel file field a is treated as a string instead of a date.

Re: How do I get ODS Excel to output a date field?

Reeza — Wed, 20 Dec 2017 20:57:14 GMT

Apply a format to the date. It currently doesn't have one assigned, even in the SAS data set it looks like a numeric value.

Re: How do I get ODS Excel to output a date field?

Tom — Wed, 20 Dec 2017 20:59:57 GMT

Huh? First thing is to make it look like a date to SAS before sending it to Excel.

data foo;
  input id a date9.;
  format a date9. ;
datalines;
1 01Jan2017
2 02Jan2017
3 01Jan2006
4 31Dec2006
;

Now try exporting using PROC PRINT.

ods excel file="&path/test_date.xlsx";

proc print data=foo noobs;
run;

ods excel close;

Looks fine to me.

Re: How do I get ODS Excel to output a date field?

tomcmacdonald — Wed, 20 Dec 2017 21:10:07 GMT

I thought SAS treated dates as numeric and there is no date datatype. Shouldn't it really be date or datetime informats because that's how this numeric value is represented?

Re: How do I get ODS Excel to output a date field?

Reeza — Wed, 20 Dec 2017 21:12:10 GMT

INFORMATS control how SAS reads in a variable.

FORMATS control how SAS displays a variable.

There is no date data type, but there are date formats, a numeric variable with a date format will be treated as a date in Excel. If you want Excel to recognize it as a date use the mmddyy10. format.

@tomcmacdonald wrote:
I thought SAS treated dates as numeric and there is no date datatype. Shouldn't it really be date or datetime informats because that's how this numeric value is represented?

Re: How do I get ODS Excel to output a date field?

tomcmacdonald — Wed, 20 Dec 2017 21:13:21 GMT

OK, thank you.