topic Re: ODS Excel - DATETIME values not displaying properly in ODS and Base Reporting

ODS Excel - DATETIME values not displaying properly

DingoChutney — Tue, 09 Jan 2018 10:17:13 GMT

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/highlight/true#M13639

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.

Re: ODS Excel - DATETIME values not displaying properly

Ksharp — Tue, 09 Jan 2018 12:19:10 GMT

Not sure if it could work.

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

Re: ODS Excel - DATETIME values not displaying properly

DingoChutney — Tue, 09 Jan 2018 13:28:28 GMT

@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!

Re: ODS Excel - DATETIME values not displaying properly

Reeza — Tue, 09 Jan 2018 16:21:57 GMT

Try having both the SAS format and the tagattr.

define testdate	/  format = dtspec. style(column)={tagattr='format:dd/mm/yy;@'} group left;

Re: ODS Excel - DATETIME values not displaying properly

Reeza — Tue, 09 Jan 2018 16:26:42 GMT

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

Re: ODS Excel - DATETIME values not displaying properly

DingoChutney — Wed, 10 Jan 2018 10:36:12 GMT

@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!

Re: ODS Excel - DATETIME values not displaying properly

DingoChutney — Wed, 10 Jan 2018 11:26:30 GMT

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;