topic Re: PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT in SAS Programming

PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT

FarazA_Qureshi — Wed, 18 Sep 2013 21:43:32 GMT

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.

Re: PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT

Reeza — Wed, 18 Sep 2013 21:47:32 GMT

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;

Re: PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT

ballardw — Wed, 18 Sep 2013 22:04:24 GMT

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.

Re: PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT

FarazA_Qureshi — Wed, 18 Sep 2013 22:17:46 GMT

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

Re: PROPER EXCELXP ODS TAGS FOR VERTICAL ALIGNMENT

FarazA_Qureshi — Thu, 19 Sep 2013 06:19:58 GMT

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?