BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tom
Super User Tom
Super User

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
Steelers_In_DC
Barite | Level 11

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
Super User Tom
Super User

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
Barite | Level 11

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
Super User Tom
Super User

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
Super User

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
Super User

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
Super User Tom
Super User

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

 

 

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
  • 7 replies
  • 4319 views
  • 0 likes
  • 3 in conversation