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
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.
Not sure if it could work.
style(column)={tagattr='format:dd/mm/yy;@ type:datetime '}
@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!
Try having both the SAS format and the tagattr.
define testdate / format = dtspec. style(column)={tagattr='format:dd/mm/yy;@'} group left;
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
@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!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.