BookmarkSubscribeRSS Feed
FarazA_Qureshi
Calcite | Level 5

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.

4 REPLIES 4
Reeza
Super User

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;

FarazA_Qureshi
Calcite | Level 5

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?

ballardw
Super User

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.

FarazA_Qureshi
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 932 views
  • 6 likes
  • 3 in conversation