Proc Report - Coloring for Spanning Headers

[neha_sas]

Hi,

I want to create a Excel report which would have data output from SAS dataset. I am using Proc report through ODS Tagset.ExcelXP to write the data in an Excel. I am facing a couple of issues while trying to complete this

1) Report contains 3 levels of Spanning headers and I would need different background colors for all the headers.

I was trying the Traffic Lighting way

proc format;

     value $color

          'REPORT' = '#9999FF'

          'Australia' = '#FF6600'

     other = '#FF6600';

run;

ods listing close;

ods tagsets.excelxp file = 'report.xls' options ( Autofilter = 'YES' absolute_column_width = "11,14,........) style = sansprinter;

proc report data = sfin.reportfin nowd style(header) = { background = $color. font_size= 10pt};

column ('REPORT'('Australia' ABC DEF XYZ ('States' X Y Z (....))));

display ABC

....

..

run;

but the headers do not take the desired background values. Not sure if this appoach is correct.

2) Inline formatting using ods escapechar not working.Although I am not getting any errors in the log.

3) This report would be generated bimonthly and as part of the next iteration, I would want to open the same excel sheet and wrtie out the results. all this would happen dynamically, meaning I would not know how many rows would get populated in the spreadsheet.The challenge here is that I am not finding any option that allows me to open an existing spreadsheet to write out the results.Any suggestions?

[Cynthia_sas]

HI,

Last question first--TAGSETS.EXCELXP  only creates a new file-- it will NOT add to an existing workbook.

THere are ways to make fake spanning headers by using ACROSS usage, but i am not able to post code right now.

Yr format method won't work because you do not have data values right now, you only have text strings.

Cynthia

[neha_sas]

Thanks Cynthia@sas. So is there any other way in SAS that I can follow to open an existing excel sheet and write the results into it with all the formatting that I am now doing with tagsets.excelxp?

Thanks

[Tim_SAS]

This works for me:

ods escapechar = '^';

ods tagsets.excelxp file="test.xml";

proc report data=sashelp.class;

col ('^{style green header}' name age sex) ('^{style red header}' height weight);

define name--weight / display  style(header)={background=black color=white};

run;

ods tagsets.excelxp close;

[neha_sas]

Hey Tim,

I tried your code but its not working at my end. Not sure why this is happening but am not getting any headers ...The header text is just missing and I have BLANK headers...

Thanks

[Tim_SAS]

Try my example but change the destination to HTML. If that works, then you're probably not using the latest version of the ExcelXP tagset. See http://support.sas.com/rnd/base/ods/odsmarkup/index.html. If my example doesn't work with HTML, or you already have the latest version of the ExcelXP tagset, then you should probably take this up with Technical Support.

[Bob_Wilcox]

I'm having exactly the same results. Has anyone figured out a way to get this to work?

[Cynthia_sas]

Hi:

  Look at the example of using ACROSS to style the spanning headers in this paper (http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf) starting on page 11 and continuing to page 13.

cynthia

[Bob_Wilcox]

Cynthia, this is sooooooo close. is there any way that I can email my results, so you can see what's happening?

[Cynthia_sas]

Hi:

  I'd prefer to put examples here instead of emailing, that way, everybody who's interested learns something and people who are searching in the future can read the conclusion of the thread. It's possible to mock up a sample program based on SASHELP.CLASS. Your idea to use formats was a clever one. I knew that formats would work for some headers, but didn't really expect that the technique would work for spanning headers, too. So, that was a good idea.

  Here's something else to try. The program shows that you can use the format, with the style(header) in the PROC REPORT statement (see #1) or you can do something like shown in #2, where you can use a slightly different format, but override a particular value, in a DEFINE statement, too.

Cynthia

proc format;

     value $color

          'REPORT' = '#9999FF'

          'Australia' = '#FF6600'

          'States' = 'pink'

          'Wombat' = 'lightgreen'

          other = 'lightblue';

     value $altclr

          'REPORT' = '#9999FF'

          'Australia' = '#FF6600'

          'States', 'Height', 'Weight' = 'pink'

          'Wombat', 'Name', 'Age', 'Sex' = 'lightgreen'

          other = 'lightblue';

run;

ods listing close;

ods tagsets.excelxp file = 'c:\temp\report.xml'

    options ( embedded_titles='yes') style = sansprinter;

proc report data = sashelp.class(obs=3) nowd

   style(header) = { background = $color. font_size= 10pt};

   title 'All Headers Different Colors Based on Formats';

   column ('REPORT'('Australia' ('Wombat' name age sex )('States' height weight)));

run;

proc report data = sashelp.class(obs=3) nowd

   style(header) = { background = $altclr. font_size= 10pt};

   title 'Some Headers Same Colors Based on Formats (one header diff)';

   column ('REPORT'('Australia' ('Wombat' name age sex )('States' height weight)));

   define name / 'Name';

   define age / 'Age';

   define sex / 'Sex';

   define height / 'Height';

   define weight / 'Weight'  style(header)={background=lightyellow};

run;

ods _all_ close;

[Bob_Wilcox]

That did it! Thaks so much for all of your help.

[Dob4Die]

Hi Cynthia ,

This is great document , very helpful.

I have quick Question about it.

I ran your code on windows SAS 9.3 and it worked perfectly. Where as the same code on SAS GRID (Linux Environment) , columns are not holding the color information . Do we need to something differently for it ? Let me know. Thanks.

PROC FORMAT for Colors not working , where as if I give hardcoded color values , its working fine.

[Cynthia_sas]

Hi:

  First, it's not a good idea to add your question to a 9 month old post. It's better to start a new post and put a link to the post you are referenceing. I am teaching this week and don't have time to run the code extensively. I do not have a GRID/Linux installation to test with. If you are saying that you ran the EXACT code above on a different operating system and you do NOT get the results shown in the screen shot, then that is a question for Tech Support. When I run the EXACT code in SAS 9.4 in Enterprise Guide and in SAS on Windows, I get the EXACT same results.

cynthia