Write DATETIME values to ODS EXCEL

[Tom]

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;

[Steelers_In_DC]

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

var datetime / style(data)={tagattr='type:DateTime format:YYYY-MM-DD'};

Mark

[Tom]

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.

[Steelers_In_DC]

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='type:DateTime 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

[Tom]

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.

[Ksharp]

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

[Ksharp]

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

[Tom · Accepted solution]

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