The SAS Output Delivery System and reporting techniques

Tabulate, Tagsets.ExcelXp and Dates

Reply
Contributor
Posts: 61

Tabulate, Tagsets.ExcelXp and Dates

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;

SAS 9.3, Windows XP, Excel 2010]

SAS Super FREQ
Posts: 8,645

Re: Tabulate, Tagsets.ExcelXp and Dates

Hi:

Interesting code. I did not know that the NORMAL style had a style element called "data_date9". But, even if it does, the style override is being applied to the column HEADER for both STOCK and DATE variables -- these are not the CLASS level cells, but the cells that say "STOCK" or "DATE" -- so even if your style element "data_date9" contains some kind of instruction, I think you are applying the override to the wrong statement in TABULATE. I would expect to see a TAGATTR style override on a CLASSLEV statement for DATE, but not for STOCK (which is not a date variable).

  You should be able to pass in a TAGATTR style attribute using TAGSETS.EXCELXP, such that you actually send a Microsoft format to Excel from SAS, ODS and PROC TABULATE. I have several examples of this in my SGF 2011 paper "Don't Gamble with Your Output: How to Use Microsoft Formats with ODS" available at (http://support.sas.com/resources/papers/proceedings11/266-2011.pdf". I imagine that you might have created a custom style template that contained the TAGATTR style attribute inside the date_date9 style element definition, but I don't usually recommend calling a custom style by the same name as a SAS style template.

  I think that the best thing for you to do would be to work with Tech Support on this. If you try any of the TAGSETS.EXCELXP examples in the paper, you should find that Excel interprets the SAS date field as a numeric date using the format you want. And, actually, in SAS 9.3 and 9.4, I have found that TAGSETS.EXCELXP seems to respect a SAS date format, so I wonder what would happen if you just had a FORMAT statement for the DATE variable in the code and took out the style override. But I think that Tech Support needs to be consulted because I'm not sure that your code will produce the desired results, as your program is currently written.

cynthia

Contributor
Posts: 61

Re: Tabulate, Tagsets.ExcelXp and Dates

/style=data_date9 was a relic from an earlier version and doesn't do anything on this.  This was a quick example put together to post on here.

SAS Super FREQ
Posts: 8,645

Re: Tabulate, Tagsets.ExcelXp and Dates

Hi:

  Ah, it was a distracting addition, because IF your style element did have a reference to TAGATTR, then it would be incorrectly applied to STOCK (which is a character variable). However, using SAS 9.4 and TAGSETS.EXCELXP, I find that my SAS format for date variables is used correctly when I open the XML file with Excel. See the attached screen shot, generated with the code below. Note that all I did was use a SAS FORMAT statement for DATE. Even my custom PICTURE format was respected (although, I admit, it's not a very interesting date format).

  So, based on the code you posted, do I infer correctly that you wanted the DATE class level headers to be formatted with a DATE9. format? In my example, I changed the format for the DATE headers just using the SAS FORMAT statement.

cynthia

*** code;

ods tagsets.excelxp file='c:\temp\fmtdate.xml' style=sasweb

    options(embedded_titles='yes' doc='Help' sheet_interval='none');

proc tabulate data=sashelp.stocks missing;

title '1a) Use SAS format MMDDYY10.';

where year(date) = 2001 and month(date) le 6;

class stock date;

var high low;

tables stock*(high low),date=''/row = float;

keylabel sum=' ';

format date mmddyy10.;

run;

proc tabulate data=sashelp.stocks missing;

title '1b) Use SAS format DATE9.';

where year(date) = 2001 and month(date) le 6;

class stock date;

var high low;

tables stock*(high low),date=''/row = float;

keylabel sum=' ';

format date date9.;

run;

proc format;

  picture picd low-high='%Y %12B %0d' (datatype=date) ;

run;

    

proc tabulate data=sashelp.stocks missing;

title '1c) SAS Picture Format is respected';

where year(date) = 2001 and month(date) le 6;

class stock date;

var high low;

tables stock*(high low),date=''/row = float;

keylabel sum=' ';

format date picd24.;

run;

 

ods tagsets.excelxp close;

Attachment
Contributor
Posts: 69

Re: Tabulate, Tagsets.ExcelXp and Dates

We tackled this problem.

The solution is to write the date out as YYMMDD10. and apply an Excel format. This sample below uses PROC REPORT, but you might be able to adapt it for PROC TABULATE output by applying styles on your date variables. This exanple writes out SAS dates as DD MON YYYY, e.g. 5 Jun 2014. In Excel it is recognised as a numeric Excel date.

PROC REPORT etc... ;

define notified_date / display format=yymmdd10. 'Notified Date' style(column)=[tagattr='typeSmiley Very HappyateTime formatSmiley Very Happy MMM YYYY;@'];

RUN;

Post a Question
Discussion Stats
  • 4 replies
  • 1353 views
  • 0 likes
  • 3 in conversation