The SAS Output Delivery System and reporting techniques

PROC REPORT AND ODS EXCELXP STYLE OPTIONS

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

PROC REPORT AND ODS EXCELXP STYLE OPTIONS

[ Edited ]

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:

 

 my_current_output.png

 

  1. 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.
  2. 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.
  3. 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:

 

my_desired_output.png

 Thank you for the help.

 

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

 

 


Accepted Solutions
Solution
‎02-11-2016 12:08 PM
Super User
Posts: 11,114

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

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)

View solution in original post


All Replies
Super User
Posts: 11,114

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

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.

Contributor
Posts: 42

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

[ Edited ]

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.

Super User
Posts: 11,114

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

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;
Contributor
Posts: 42

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

[ Edited ]

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?

Solution
‎02-11-2016 12:08 PM
Super User
Posts: 11,114

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

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)

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1167 views
  • 1 like
  • 2 in conversation