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

[tomcmacdonald]

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.

[Reeza · Accepted solution]

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.

[tomcmacdonald]

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?

[Reeza]

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?

---

[tomcmacdonald]

OK, thank you.

[Tom]

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.