The SAS Output Delivery System and reporting techniques

ODS Excel - DATETIME values not displaying properly

Reply
New Contributor
Posts: 4

ODS Excel - DATETIME values not displaying properly

[ Edited ]

Hi there,

 

The thread below suggests that the issue of datetime values formatted as General when using ODS Excel would be addressed in the M3 patch. However, I'm encountering the issue on a server patched to M4, so is it still a problem?

 

EDIT: Server version: 9.04.01M4P110916

 

https://communities.sas.com/t5/ODS-and-Base-Reporting/Write-DATETIME-values-to-ODS-EXCEL/m-p/205533/...

 

My code:

 

proc format lib=work;
	picture dtspec 
		. = ' '
 		other='%0d/%0m/%Y %0H:%0M:%0S' (datatype=datetime );
	;
run;

data date_test(keep=testdate); 
	format testdate dtspec.;
	testdate='01JAN2018 08:36:09'dt;	
run;

ods listing close;
ods excel file="/<path>/testdates.xlsx";

proc report data = date_test;
	title j=l "Test";
	column 	testdate;
	define testdate	/  style(column)={tagattr='format:dd/mm/yy;@'} group left;
run;
ods excel close;
ods listing;

Much like the OP above, editing the cell with F2 and pressing enter forces the format to resolve, but it initially displays as General. Any suggestions at all would be welcome. I'd like to use ODS Excel over ExcelXP purely for its native support of the XLSX file type (removes the need for a conversion script after each ODS step)/

 

 

Thanks in advance.

Super User
Posts: 10,623

Re: ODS Excel - DATETIME values not displaying properly

Posted in reply to DingoChutney

Not sure if it could work.

 

 style(column)={tagattr='format:dd/mm/yy;@  type:datetime '}
New Contributor
Posts: 4

Re: ODS Excel - DATETIME values not displaying properly


Ksharp wrote:

Not sure if it could work.

 

 style(column)={tagattr='format:dd/mm/yy;@  type:datetime '}

Hi there, cheers for replying. I'm afraid I'd tried it with and without the type clause. No difference!

Super User
Posts: 22,874

Re: ODS Excel - DATETIME values not displaying properly

Posted in reply to DingoChutney

Try having both the SAS format and the tagattr.

 

define testdate	/  format = dtspec. style(column)={tagattr='format:dd/mm/yy;@'} group left;
Super User
Posts: 22,874

Re: ODS Excel - DATETIME values not displaying properly

I'm 9.4 M3 and all of those examples worked fine, the date showed up exactly as it was shown in the code. 

Were you looking for something else?

 

SAS 9.4 TS1M3, Windows 7, Office 2010

New Contributor
Posts: 4

Re: ODS Excel - DATETIME values not displaying properly


Reeza wrote:

I'm 9.4 M3 and all of those examples worked fine, the date showed up exactly as it was shown in the code. 

Were you looking for something else?

 

SAS 9.4 TS1M3, Windows 7, Office 2010


None of them work for me, so I'm wondering if there's something else amiss? This is being executed on Linux, perhaps whatever fix was introduced in M3 on Windows hasn't yet been applied on other OSs? Or maybe it's an Excel issue? I'm stumped to be honest.

 

Thanks for your suggestions though!

New Contributor
Posts: 4

Re: ODS Excel - DATETIME values not displaying properly

Posted in reply to DingoChutney

I did some more testing. Dates work fine - no tagattr clause needed, just a properly formatted SAS date. Also, converting the datetime to an excel numeric representation of a datetime works - I used tagattr and set the type as Number with a date format. See below:

 

proc format lib=work;
	picture dtspec 
		. = ' '
 		other='%0d/%0m/%Y %0H:%0M:%0S' (datatype=datetime );
	;
run;

data date_test(keep=test:); 
	format testdatetime dtspec. testdate ddmmyy10.;
	testdatetime='01JAN2018 08:36:09'dt;	
	testdate = today();
	testxldate = 43101.3723263889;
run;

ods listing close;
ods excel file="/<path>/testdates.xlsx";

proc report data = date_test;
	title j=l "Test";
	column 	testdate testdatetime testxldate;
	define testdate	/ display;
	define testdatetime	/ style(column)={tagattr='type:Date format:dd/mm/yy;'} display;
	define testxldate	/ style(column)={tagattr='type:Number format:dd/mm/yy;'} display;
run;
ods excel close;
ods listing;
Ask a Question
Discussion stats
  • 6 replies
  • 225 views
  • 0 likes
  • 3 in conversation