The SAS Output Delivery System and reporting techniques

Write DATETIME values to ODS EXCEL

Accepted Solution Solved
Reply
Super User
Super User
Posts: 6,382
Accepted Solution

Write DATETIME values to ODS EXCEL

How can I get EXCEL to recognize my date time values as valid date time values in Excel?  Everything I try just ends up with Excel treating the value as a text string.  If I use the format MDYAMPM. then at least Excel will recognize it is a date AFTER I edit the cell.  But I cannot get it to automatically create datetime values, even if I use TAGATTR to set the type.

data example;

  a=date();

  b=datetime();

run;

ods excel file="example.xlsx" ;

proc print data=example;

  var a ;

  var b / style(data)={tagattr='type:datetime'};

  format a yymmdd10. ;

  format b mdyampm. ;

run;

ods excel close;


Accepted Solutions
Solution
‎07-20-2015 11:45 AM
Super User
Super User
Posts: 6,382

Re: Write DATETIME values to ODS EXCEL

I got this response from SAS Support.  Looks like it will be fixed when it is made production with the next maintenance release of SAS.


Hello Tom,

The issue with with the DateTime and the Time field coming over as character was also a defect with the pre-production version of the Excel destination which also was fixed for the production version of the Excel destination which will be available with SAS 9.4M3. Sorry for the inconvenience here.

SIncerely,

Chevell

View solution in original post


All Replies
Valued Guide
Posts: 856

Re: Write DATETIME values to ODS EXCEL

I'm unable to test this now but try adding a format.

var datetime / style(data)={tagattr='typeSmiley Very HappyateTime format:YYYY-MM-DD'};

Mark

Super User
Super User
Posts: 6,382

Re: Write DATETIME values to ODS EXCEL

You can attached the format, but it does not work because Excel is still treating the cell value as text. If you edit the cell in Excel (F2 and then ENTER) it converts the value to a number and applies the format.

Valued Guide
Posts: 856

Re: Write DATETIME values to ODS EXCEL

I just tested it and this is working. (I think)  The format says general but I think this is the output you are looking for.  The format line I put before did not work but ods I am using does, I guess it's the ExcelXP :

data test;

format datetime datetime21.;

datetime = today();

run;

ods _all_ close;   

ods tagsets.ExcelXP path='/export/home/pp78499/sasuser.v92' file='datetime.xml'   

style=printer;   

ods tagsets.ExcelXP options(sheet_name='datetime' AUTOFILTER = 'ALL');    

proc print data=test noobs;       

/*var datetime / style(data)={tagattr='typeSmiley Very HappyateTime format:YYYY-MM-DD'};run;quit;    */

var _all_;run;quit;   

ods tagsets.ExcelXP options(sheet_name='datetime' AUTOFILTER = 'ALL');   

ods tagsets.ExcelXP close;

Hope this helps

Super User
Super User
Posts: 6,382

Re: Write DATETIME values to ODS EXCEL

I could get it to work with TAGSETS.EXCELXP, but that is not generating an actual XLSX file.

I many ways the ODS EXCEL destination does a better job of automatically setting the format of the Excel cells.  But I can't get it to handle datetime values.

Grand Advisor
Posts: 9,596

Re: Write DATETIME values to ODS EXCEL

1) For TAGSETS.EXCELXP, I remember there is a macro in support.sas.com to transform XML into native Excel file . Reeza point it out before.

2)Interesting thing is proc export + xlsx can get that working .Don't know why ODS EXCEL can't do that, maybe ODS EXCEL is still a testing product.


proc export data=example outfile="/folders/myfolders/example.xlsx" dbms=xlsx replace;run;

Xia Keshan

Grand Advisor
Posts: 9,596

Re: Write DATETIME values to ODS EXCEL

Tom,

I just figure it out . You need a special format for datatime type variable, like DTDATE. It seems EXCEL don't have datetime type .

Code: Program

data example;
  a=date();
  b=datetime();
  format a yymmdd10. ;
  format b dtdate. ;
run;
ods excel file="/folders/myfolders/example.xlsx";
proc print data=example;
  var a ;
  var b / style(data)={tagattr='format:yyyy/m/d'};
run;
ods excel close;

Xia Keshan

Solution
‎07-20-2015 11:45 AM
Super User
Super User
Posts: 6,382

Re: Write DATETIME values to ODS EXCEL

I got this response from SAS Support.  Looks like it will be fixed when it is made production with the next maintenance release of SAS.


Hello Tom,

The issue with with the DateTime and the Time field coming over as character was also a defect with the pre-production version of the Excel destination which also was fixed for the production version of the Excel destination which will be available with SAS 9.4M3. Sorry for the inconvenience here.

SIncerely,

Chevell

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 1077 views
  • 0 likes
  • 3 in conversation