BookmarkSubscribeRSS Feed
DingoChutney
Calcite | Level 5

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.

6 REPLIES 6
Ksharp
Super User

Not sure if it could work.

 

 style(column)={tagattr='format:dd/mm/yy;@  type:datetime '}
DingoChutney
Calcite | Level 5

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

Reeza
Super User

Try having both the SAS format and the tagattr.

 

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

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

DingoChutney
Calcite | Level 5

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

DingoChutney
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1436 views
  • 0 likes
  • 3 in conversation