Change to SAS version 9.2 and loss of a line in ODS to XLS with proc report

[SheilaM]

My site went to version SAS 9.2 recently and that is when this problem began.  The code ran just fine before the upgrade.  The basic problem is simple:  I am using proc report and ODS tagsets.excelxp to creat a series of tables.  Each table should have sections with a solid black row below it.  Instead, I get two rows after each section, which are white.

This is a samlpe of the code I use:

data SR_2012 ;

          SET pullin.ALL3_NUMS ;

          run;

           ods escapechar='~' ;

           ods tagsets.ExcelXP

             options

            ( .....);

  proc sort data=SR_2012 ;  by group_num  item_num; run;

%macro mac_procs (varad,varan,vardis,varad1,varan1,vardis1,label_ad,label_an,label_dis);

  proc report data=SR_2012  nowd   headline headskip split="#"

             style(header)=[background=#FFA07A      foreground=Black ];

  column   group_num  cat_label item_num item_lab  

           ad_All  an_All dis_All &varad &varan &vardis ;

    define  group_num  / 'Group#Num' style(column)=[background=#FFFFCC] group width=5  right;  

      define  cat_label /  'Item#Category' style(column)=[background=#FFFFCC]  group width=10   left;

      define  item_num  / 'Item#Num' style(column)=[background=#FFFFCC]  group width=5   left;

    define  item_lab  / 'Item#Value' style(column)=[background=#FFFFCC]  group width=31  left;

      ***Six more define statements***;

      **This is where the code in question begins**;

            break after group_num   /  suppress

            style={background=Black};

            compute after group_num ; 

                    line '~{S [cellheight=4 background=Black] }';

            endcomp;

            ** a series of proc reports in macros**;

            run;       

            ods tagsets.ExcelXP close;

         ods listing;

         quit;

Of course, I started off using skip in the break statement.  It has never worked.   I have tried  " line '  '; "  Also,  "line '~{ style [cellheight=4 background=#333333] }.  Neither of these made a difference.

One last bit of information, when I placed an out= statement on the proc report, the report showed a line with _Break_=group_num (which I expected) and each line repeated the values of the very first line in the tables.

[Cynthia_sas]

Hi:

  SKIP (along with other options, like HEADLINE, HEADSKIP, SPACING, BOX, PANELS, DOL, DUL, OL, UL, WIDTH=) is a LISTING only option. It was never supported by ODS destinations. So, PROC REPORT is working as designed. Destinations which support style attribute changes (colors and fonts) do not behave the same way as the LISTING destination because those options (like SKIP, etc) were designed to work with LISTING to make certain cosmetic changes possible in the LISTING window. Each ODS destination has a way to interpret style changes. And so, from the very beginning, LISTING only options have been ignored by ODS -- not just for PROC REPORT, but for other procedures, like PRINT and TABULATE as well.

  The BREAK AFTER will probably not produce a line unless you actually have SUMMARIZE on the BREAK statement. The LINE statement when I use SAS 9.3 only produces 1 line. However, this Tech Support note explains that style attributes on a LINE statement are not currently used with the TAGSETS.EXCELXP destination: http://support.sas.com/kb/36/442.html

  If you encounter code that worked in one version of SAS, but does not work in a new version of SAS, this is a good reason to work with Tech Support on your issue. If your issue is related to Tech Support notes that have already been published, then Tech Support would tell you whether there was a workaround for the issue.

  I would recommend that you make sure you have the most current version of TAGSETS.EXCELXP, first (see below). But if you are still encountering differences, then Tech Support can open a track and if this is a bug, they can open a defect on the bug and notify the developer. Also, Tech Support has the ability to test your code on different versions of SAS.

  To check to see whether you have the most updated version of TAGSETS.EXCELXP, I would recommend that you refer to this Tech Support note:

http://support.sas.com/kb/32/394.html

  To open a track with Tech Support, fill out the form at this link:

http://support.sas.com/ctx/supportform/createForm

cynthia