BookmarkSubscribeRSS Feed
wcpatton
Calcite | Level 5

I have example code below.  What I'm trying to do is get DATE in my output spreadsheet that Excel actually sees as a date, rather than a text field.

ods listing;

options missing = 0;

ods tagsets.excelxp

file= "C:\Sheet3.xml"

  style=normal;

proc tabulate data=sashelp.stocks missing;

class stock date/style=data_date9;

var high low;

tables (high low),date=''

/row = float;

run;

ODS TAGSETS.EXCELXP CLOSE;

[ETA: SAS 9.3, Windows XP, Excel 2010]

17 REPLIES 17
snoopy369
Barite | Level 11

I'm not actually sure how to do the headers in PROC TABULATE.  Using TAGATTR you can tell Excel to treat a number like a date, but that doesn't seem to take effect in headers for some reason I'm not entirely sure of.  You could output the tabulate to a dataset and then PROC REPORT that out, that should work at least.

wcpatton
Calcite | Level 5

So far, my options have been to either output the date as a number for excel to read (you have to add something, like 21000 to it), or to use a picture format and output it as text and then excel won't recognize it as a date, but at least it will look like one.

snoopy369
Barite | Level 11

For me it actually will appear as as date with any normal date format, and when excel sees it, it looks like I ask it to - but as text.  However if you click in the cell and hit enter, it turns it into an excel date.

wcpatton
Calcite | Level 5

No, I'm with you there.  The problem is that if I want to use excel formulas or graphs or anything that wants it to be a date, that doesn't work.  I can make a formula in excel that dissects the text..

wcpatton
Calcite | Level 5

Bumping because I still have this issue.

Reeza
Super User

The tagattr option should fix that. Please, post the code you've tried.

This is a great reference for Tagsets to help resolve issues:

http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf

wcpatton
Calcite | Level 5

I posted an example in the OP.

Reeza
Super User

Repost in the ODS section, I don't think Cynthia monitors this section and she's probably the one who will have the definitive answer.

snoopy369
Barite | Level 11

I'm fairly sure there is no good answer, short of modifying the template or some other hack - it's an oddity in how excel works relating to the open xml format.  ODS EXCEL may fix this, once I can get my hands on a copy of 9.4 TS1M1 I will confirm (probably not for a few weeks), but that obviously doesn't help a 9.3 user.

Reeza
Super User

you can use tagattr, but mine says' the file is corrupt so doing something wrong....

wcpatton
Calcite | Level 5

Same here, I get the TABLE error when I use Tagattr.    

snoopy369
Barite | Level 11

I don't think so.  See my first response.  I don't have the code I used to reverify this when this was originally posted, but from what I recall, Excel won't display the date properly until the cell is selected and validated (hit enter).  This was the case for me a year or two ago when I first hit this issue, and I verified it back when this question was originally posted.

snoopy369
Barite | Level 11

Hmm, or maybe not.  Does this work the way you want it to?

edit: I think this is sort-of-working, but the format still changes when you select the cell and hit 'enter' - but at least it's closer.  Not sure if this is the 'does not work' I remember earlier or if this is an improvement.

IE, if you go to cell D1, select, hit enter, it goes from "02SEP86" to "2-Sep-86".  But, at least it's a real date value you can do math on and such.

proc template;

define style test;

parent=styles.normal;

class date/

  tagattr="m/d/yyyy";

end;

quit;

ods listing;

options missing = 0;

ods tagsets.excelxp

file= "C:\temp\test.xml"

  style=test;

proc tabulate data=sashelp.stocks missing;

class stock date/style=data_date9;

var high low;

tables (high low),date=''*{style=date}

/row = float;

run;

ODS TAGSETS.EXCELXP CLOSE;

wcpatton
Calcite | Level 5

Almost.  The Vars are coming out as date style now, as well.

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