PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT

Reply
Contributor
Posts: 51

PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT

While having a piece of following code applied to export data to excel in ODS manner sure would oblige if any of you experts could advise me a good way to apply a simple format like top aligning the text and number format with 0 (Zero) decimal places and no background color:

ODS tagsets.excelxp FILE="ExcelResultsC.xls";

PROC TABULATE DATA=RESULTS2 OUT=CHK2;

CLASS PRODUCT DPD_BUCKET DATA_MONTH;

VAR TOTAL_OS COUNT_OS;

TABLE PRODUCT, PRODUCT*DPD_BUCKET, DATA_MONTH*TOTAL_OS DATA_MONTH*COUNT_OS;

RUN;

ods tagsets.excelxp close;

What would be the appropriate code of defining a style.

Thanx in advance.

Super User
Posts: 17,912

Re: PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT

Have you tried some of the default SAS styles to see if they come close to what you want? Style=journal and style=meadow are two that I like.

I believe to have a number format, you can try formatting it in SAS first, with a format statement or use tag attr in excel.

Try this reference and then look up the appropriate paper for what you're trying to do.

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

ODS tagsets.excelxp FILE="ExcelResultsC.xls" style=journal;

PROC TABULATE DATA=RESULTS2 OUT=CHK2;

CLASS PRODUCT DPD_BUCKET DATA_MONTH;

VAR TOTAL_OS COUNT_OS;

TABLE PRODUCT, PRODUCT*DPD_BUCKET, DATA_MONTH*TOTAL_OS DATA_MONTH*COUNT_OS;

RUN;

ods tagsets.excelxp close;

Contributor
Posts: 51

Re: PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT

Thanx for the helpful reply Reeza! However the style meadow leads to an error upon opening the file in Excel while the journal opens well.

Any reason?

Super User
Posts: 10,538

Re: PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT

If you're not using the output data set you might as well drop the code OUT_CHK2;

You can set the default display for all statistics by using the option format = f8.0 on the proc tabulate statement, or if the numbers are larger increase the 8. If you want them displayed with commas then F=Comma8.0.

Colors as Reeza says by default come from the Style in effect which can be specified on either the Proc Tabulate or the ODS statement.

Which text do you need Top Aligned? Again may be set in Style overides for specific variables or output.

Contributor
Posts: 51

Re: PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT

Thanx again for your help. Top alignment is required for the Row Headings.

Ask a Question
Discussion stats
  • 4 replies
  • 313 views
  • 6 likes
  • 3 in conversation