Tabulate, Tagsets.ExcelXp and Dates

[wcpatton]

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]

[snoopy369]

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]

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]

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]

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]

Bumping because I still have this issue.

[Reeza]

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]

I posted an example in the OP.

[Reeza]

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]

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]

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

[wcpatton]

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

[snoopy369]

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]

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]

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