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]
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.
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.
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.
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..
Bumping because I still have this issue.
The tagattr option should fix that. Please, post the code you've tried.
This is a great reference for Tagsets to help resolve issues:
I posted an example in the OP.
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.
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.
Same here, I get the TABLE error when I use Tagattr.
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.
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;
Almost. The Vars are coming out as date style now, as well.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: