topic Re: Tabulate, Tagsets.ExcelXp and Dates in SAS Programming

Tabulate, Tagsets.ExcelXp and Dates

wcpatton — Tue, 24 Sep 2013 20:17:24 GMT

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]

Re: Tabulate, Tagsets.ExcelXp and Dates

snoopy369 — Tue, 24 Sep 2013 21:43:43 GMT

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.

Re: Tabulate, Tagsets.ExcelXp and Dates

wcpatton — Thu, 26 Sep 2013 13:28:16 GMT

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.

Re: Tabulate, Tagsets.ExcelXp and Dates

snoopy369 — Thu, 26 Sep 2013 14:38:11 GMT

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.

Re: Tabulate, Tagsets.ExcelXp and Dates

wcpatton — Thu, 26 Sep 2013 14:41:04 GMT

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..

Re: Tabulate, Tagsets.ExcelXp and Dates

wcpatton — Wed, 15 Jan 2014 18:46:30 GMT

Bumping because I still have this issue.

Re: Tabulate, Tagsets.ExcelXp and Dates

Reeza — Wed, 15 Jan 2014 20:39:49 GMT

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

Re: Tabulate, Tagsets.ExcelXp and Dates

wcpatton — Wed, 15 Jan 2014 20:40:52 GMT

I posted an example in the OP.

Re: Tabulate, Tagsets.ExcelXp and Dates

Reeza — Wed, 15 Jan 2014 20:56:49 GMT

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.

Re: Tabulate, Tagsets.ExcelXp and Dates

snoopy369 — Wed, 15 Jan 2014 21:00:56 GMT

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.

Re: Tabulate, Tagsets.ExcelXp and Dates

Reeza — Wed, 15 Jan 2014 21:14:02 GMT

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

Re: Tabulate, Tagsets.ExcelXp and Dates

wcpatton — Wed, 15 Jan 2014 21:15:26 GMT

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

Re: Tabulate, Tagsets.ExcelXp and Dates

snoopy369 — Wed, 15 Jan 2014 21:17:19 GMT

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.

Re: Tabulate, Tagsets.ExcelXp and Dates

snoopy369 — Wed, 15 Jan 2014 21:23:45 GMT

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;

Re: Tabulate, Tagsets.ExcelXp and Dates

wcpatton — Wed, 15 Jan 2014 21:37:32 GMT

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

Re: Tabulate, Tagsets.ExcelXp and Dates

snoopy369 — Wed, 15 Jan 2014 22:44:30 GMT

I'm not convinced it's working, in any event; looking at the raw XML, it's still a string - excel's just being smart about it. I am probably not using the style properly, I am a PROC REPORT guy not a PROC TABULATE guy when it comes to these sorts of things.

Re: Tabulate, Tagsets.ExcelXp and Dates

wcpatton — Thu, 16 Jan 2014 13:46:00 GMT

Yeah, I'm a tabulate guy, but SAS doesn't seem to want to play nice here. Maybe I'll have to learn REPORT for this, unless SAS wants to fix this.

Re: Tabulate, Tagsets.ExcelXp and Dates

snoopy369 — Thu, 16 Jan 2014 20:39:11 GMT

I believe that the problem is, at least for Tabulate, that the headers are technically always character - so in creating the resulting xml, they're sent as 'string'. You might be able to fix this by editing the tagset itself, though I'm not sure where I'd suggest beginning there. I don't think tagsets.excelxp will receive too much more development now that ODS EXCEL is largely out, so I wouldn't expect it to change - but who knows.