The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Highlighted
Contributor
Posts: 71
Accepted Solution

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

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.

 

 


Accepted Solutions
Solution
‎12-20-2017 04:13 PM
Super User
Posts: 22,844

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

Posted in reply to tomcmacdonald

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.

View solution in original post


All Replies
Solution
‎12-20-2017 04:13 PM
Super User
Posts: 22,844

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

Posted in reply to tomcmacdonald

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.

Contributor
Posts: 71

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

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?
Super User
Posts: 22,844

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

Posted in reply to tomcmacdonald

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?

 

Contributor
Posts: 71

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

OK, thank you.
Super User
Super User
Posts: 7,847

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

Posted in reply to tomcmacdonald

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.

image.png

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 383 views
  • 1 like
  • 3 in conversation