Missing border at end of proc report using ExcelXP

[rjroland]

Hello,

Thanks in advance for your help.  I am using ExcelXP and have a sheet where I have 3 tables.  I am able to create the 3 tables, all 3 of which use group variables.  For 2 of the tables where I want the summary, I do the rbreak/after /summarize statement and it creates a summary line with borders.

For the other table, I don't want to have a summary line but I want to have a border that is consistent with the top, left, and right side of that table.  For some reason, I can't get the bottom border to appear unless I add an rbreak/summarize statement, which I don't want however.

Basically, when I create a proc Report using ExcelXP, can I create a report with a group variable but not have a summary line but still have a solid border at the bottom of the table?  I would like to replicate the border that the rbreak/summarize statement uses without creating a summary record.

I have tried using a compute after with a call define style statement to create the border and it still won't do it.  My table looks strange in that it has a border all around the table except for at the bottom.

Thank you,

Raj

Define style styles.custom; /*Defines new style named "custom"*/

Parent=styles.minimal;        /*determines parent style to borrow qualities from*/

Style body from body     /  background=transparent borderwidth=2;

Style table from table   /  font_size=8pt

                            background=white

                            Frame=box

                            Rules=none

                            cellpadding=2

                            cellspacing=0.25

                            borderspacing=0.25

                            bordercolor=black

                            borderwidth=2;

Style header from header /  font_weight=bold 

                            font_size=10pt

                            font_style=italic

                            Frame=box

                            bordercolor=black

                            borderwidth=2;

Style footer from footer /  font_weight=bold 

                            font_size=10pt

                            font_style=italic

                            Frame=box

                            bordercolor=black

                            borderwidth=2;

End;

Run;

ods _all_ close;

ODS tagsets.ExcelXP

    File= '/home/u295529/New_Revstats_TEST.xml'

    Style=Custom;

ODS tagsets.ExcelXP options (

    Orientation='Landscape'

    Embedded_titles= 'yes'

    Embedded_footnotes= 'yes'

    Print_header= '&C&A&RPage &P of &N'

    Print_header_margin= '0.5'

    Print_footer= '&RPrinted &D at &T'

    Print_footer_margin= '0.5'

    Zoom= '85'

    Scale= '100'

    Autofit_Height= 'yes'

    Gridlines='no'

    Missing_Align= 'Center' /*Left, Center, Right (default); sets the alignment for missing values.*/

    Skip_Space= '1,0,1,1,1' /*This option controls how much space follows the different types of output that can occur within a worksheet.;

    Defaults: Table(1), Byline (0), Title (1), Footer (1), PageBreak (1) */

    ); /*Sets "global options" that will affect all worksheets in the workbook*/

ODS tagsets.ExcelXP options (

Sheet_Interval= 'None' /*Values: table (default), page, bygroup, Proc, None; determines how many tables will go in a worksheet.

Only one table is actually allowed in a workseet.  To get more than one table in a sheet, the tables are combined.

If defining a sheet Interval, make it the first option since it will closes the existing sheet and starts a new sheet*/

Sheet_Label= 'none' /*Used for sheet naming in combination with Sheet_Interval option (default = none).  Unlikely to use.*/

Sheet_name='UCH-Summary'/*Name of Excel Tab*/

Frozen_headers='7' /*Locks the specified number of rows for scrolling*/

Frozen_Rowheaders='1' /*Locks the specified number of columns for scrolling*/

Autofilter= '1' /*autofilters can be applied to one column, all columns, or to a continuous range of columns*/

AutoFilter_Table= '1,2,3' /*If multiple tables are on a page, determines which table on a page recieves the autofilter (default = 1)*/

/*Absolute_column_width= '13, 8.8, 8.8, 10.7, 10.7, 8.8, 8.8, 10.7, 10.7, 8.8, 8.8, 10.7, 8.8, 8.8, 10.7, 8.8, 8.8, 10.7, 8.8, 8.8, 10.7,

8.8, 8.8, 10.7, 8.8, 8.8, 10.7, 8.8, 8.8, 10.7, 8.8, 8.8, 10.7, 8.8, 8.8, 10.7' /*set column widths for each column*/

Width_Fudge= '0.5'

);

    Define OD/ Group 'OD' style(column)={borderleftcolor=black borderleftwidth=2};

    Define Minor_Cd/ Group 'Major' style(column)={borderleftcolor=black borderleftwidth=2};

    Define P1_Rev /Analysis noprint;

{ Series of other define field calculations};

    Compute P1_RASM;

        If P1_ASMs.sum GT 0 then P1_RASM=100*(P1_Rev.sum)/(P1_ASMs.sum);

        Else P1_RASM=0;

    Endcomp;

{ Series of other computed field calculations};

Compute after;

    call define (_row_, "style", "style={font_weight=bold borderbottomcolor=black borderbottomwidth=2 bordertopcolor=black bordertopwidth=2}");

line ' ';

EndComp;

[Cynthia_sas]

Hi:

  Perhaps some of your code got truncated when you posted it. For someone to help you, seeing ALL your code and having a sample of your data would be best. Especially since you say you have 2 or 3 PROC REPORT steps and you did not attach one complete PROC REPORT step. Nor did you explain whether you are trying the COMPUTE AFTER code in -all-PROCREPORT steps or in only 1 step. For anybody to help you, they have to replicate your style template and make enough data to try to replicate your example, using all your same options. And then they have to guess at what the rest of your code looks like. You didn't even show working template code or working PROC REPORT code, so it's really a guessing game to try to come up with a report model based on your description. To me, this sounds the kind of help you'd get from SAS Tech Support. They can look at ALL of your code and ALL of your data and they can replicate your SAS version and your OS version and really SEE what you are seeing -- not try to guess from the code snippets you've posted.

  Tagsets.Excelxp is good at creating Microsoft Spreadsheet Markup Language XML, but I find that Microsoft is "finicky" when opening and rendering the XML into workbook form, especially in the area of border lines, etc. Especially since your style template started from the MINIMAL style and you don't have many style elements redefined.

    So it could be a rendering issue. But, also, you do not mention your version of SAS or version of TAGSETS.EXCELXP that you are using (you would find this info in the SAS log) and that will be very useful to know as well, because versions of the TAGSET template changed with different versions of SAS.

  And it could be PROC REPORT. Because, if, as you say, that your second PROC REPORT does not have an RBREAK statement, then there is nothing for your border controls to be applied to. The LINE statement by itself is not sufficient. So in the reports with a summary line from RBREAK, there is -something- created by the RBREAK which can have borderlines, but otherwise, you can't put borders on something that is -not- there. One thing that you might try is to use the simple COMPUTE AFTER for reports that do have summary lines from the RBREAK, but then to use COMPUTE AFTER _PAGE_ for the PROC REPORT step that does not have any summary line. COMPUTE AFTER _PAGE_ only gets executed one time, at the very end of the REPORT step, so that might work the way you want. For the most help, you might want to work with Tech Support on this.

cynthia