topic Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS in ODS and Base Reporting

PROC REPORT AND ODS EXCELXP STYLE OPTIONS

P_S_ — Mon, 08 Feb 2016 20:21:20 GMT

Hi,

I have a question about controlling style options with PROC REPORT and ODS EXCELXP tagsets.

I am using SAS 9.4 (M2) and I output my PROC REPORT results to EXCEL using EXCELXP tagsets.

Here is my output and I am having following issues:

REGION is a variable in the dataset sashelp.shoes. When I use INLINE formatting in this variable (superscript with 3), it does NOT show up as BOLD and RED in color. You can see all other variables are in red color with bold weight. I need this in BOLD and RED.
EAST COAST and WEST COAST are NOT the variables in the dataset. These are created in COLUMN statement. The color is red and it is BOLD which is exactly what I want. But I need to control the background and want yellow and green background.
Eastern States(superscript 1) and (Western States superscript 2) are also NOT the variables in the dataset. They are also created in the COLUMN statement. Here both have INLINE FORMATTING with superscript 1 and 2 respectively. Here I lose the red color as well as bold but I need those. I also need a background of different colors (say blue and black).

This is the output I would like to have:

Thank you for the help.

THE CODE IS IN THE ATTACHMENT (code.txt).

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

ballardw — Mon, 08 Feb 2016 16:02:45 GMT

I think you want to repost your code and possibly in the insert code window (look at the {i} or SAS run icon in the menu of the display. It looks like some of your code may be getting eaten by HTML interpretter (at least in my view) as there are man missing closing brace and parentheses.

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

P_S_ — Mon, 08 Feb 2016 20:30:00 GMT

Thank you Ballardw for pointing this out.

I have now posted the code as an attachement. Hopefully that will work and people will be able to see the code.

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

ballardw — Mon, 08 Feb 2016 21:56:25 GMT

See if this helps:

ods tagsets.excelxp
file  = "D:\data\shoes_test.xml"
   style = styles.mystyle
   options (orientation    ='landscape'
            autofit_height ='yes'
            );
   ods escapechar='~';

PROC REPORT DATA=SASHELP.SHOES(obs=5)  SPLIT="/" nowd ;
   COLUMN  ("EAST COAST" "~{style foreground [color=red fontweight=bold]Eastern States~{super 1}}" Region Product Subsidiary)
           ("WEST COAST" "~{style foreground [color=red fontweight=bold]Western Stores~{super 2}}" Sales Inventory Returns );
   DEFINE  Region     / DISPLAY  "~{style foreground [color=red fontweight=bold]Region~{super 3}}" ;
   DEFINE  Product    / DISPLAY  "Product" ;
   DEFINE  Subsidiary / DISPLAY  "Subsidiary" ;
   DEFINE  Sales      / SUM      "Total Sales" ;
   DEFINE  Inventory  / SUM      "Total Inventory" ;
   DEFINE  Returns    / SUM      "Total Returns"  style(header)={background=light grey};
RUN;
ods tagsets.excelxp close;

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

P_S_ — Tue, 09 Feb 2016 18:01:19 GMT

This works. Thank you ballardw.

I also want to add the background color to the "Eastern States" - let's say a blue color. I am having difficultly with the syntax to add the background. Is there a way to add background color?

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

ballardw — Thu, 11 Feb 2016 13:31:07 GMT

Similar, just use style background [color=blue]

If combining you only need one STYLE but you have multiple options

Style background [color=blue] foreground [color=red]"text"

Look in the reference for style options you have things like font size, weight (bold and such) borders (where appropriate) justification (left right center)