proc report sums/subtotals

[alenderts]

Hi.  I'm creating a report with subtotals and exporting them to excel via ods.  Is there a way to put the subtotal at the top of the report instead of the bottom?

[Cynthia_sas]

Hi:

  If you are using PROC REPORT and the BREAK statement (or the RBREAK statement), you can control the location of the summary line(s) using:

BREAK BEFORE <brkvar> / summarize;

OR

RBREAK BEFORE / summarize;

(what you usually see in PROC REPORT is BREAK AFTER <brkvar> and RBREAK AFTER). The BREAK statement gives you a subtotal for each of a group or order variable and the RBREAK statement gives you a single "grand total". So, if you want the grand total at the top of the report, you would use RBREAK BEFORE, as shown. If you want a subtotal at the top of each GROUP, then you would use BREAK BEFORE brkvar, as shown above.

cynthia

[alenderts]

If I use proc print, I can get the subtotal row to contain the subtotal formula once it's in excel. If I use proc report, I can get a summary row at the top, but I'd like to have it contain the subtotal formula in case numbers are updated in excel.  I have a lot of excel files to create, so I'd like to have the subtotal formula inserted automatically.  Are there any options to have the formula and have it on top?

Thanks!

Angie

[Cynthia_sas]

Hi:

  How are you "using ods" to get your results into Excel: ODS HTML, ODS MSOFFICE2K, ODS MSOFFICE2K_X or ODS TAGSETS.EXCELXP?

  I believe that if you use PROC PRINT and TAGSETS.EXCELXP, then the AUTO_SUBTOTALS='yes' suboption is what inserts a formula into the subtotal cell. I do not think that will work for PROC REPORT -- the internal documentation is fairly clear that it will only work with PROC PRINT and only under certain circumstances:

(from the log)

Auto_SubTotals:   Default Value 'No'

     Values: yes, no, on, off.

     If yes, this option causes a subtotal formula to be placed in the

     subtotal cells on the last table row of the Print Procedure's tables.

     WARNING: This does not work with Sum By.  It only works if the

     totals only happen once per table.  It also does not work if the by value

     and the id value match.

  You might need to work with Tech Support to see if there is a way to insert the formula into the cell if you are using PROC REPORT and BREAK BEFORE. The issue that I see is that although TAGSETS.EXCELXP will allow you to insert a formula into a cell, you need to know the cell range in order to get the formula correct. When the subtotal is at the bottom of the group, the number of cells in the group is known (which is probably how the suboption works -- it must have an internal counter). When the subtotal is at the top of the group, the number of cells is not known.

  If you are NOT using TAGSETS.EXCELXP, then I'm not sure how you are getting a formula in the cell automatically with PROC PRINT.

cynthia

[Ksharp]

Sure. Don't forget LINE statement ,also can do this way.

Ksharp

[Cynthia_sas]

  True, they could use the LINE statement to write out text before or after a group of rows. If the OP had been interested in the LISTING destination for output, this might have worked to get the subtotal number aligned with the numbers in the column being summarized.

  However, techniques like @5 or @56 do NOT work for regular ODS destinations.  And since the OP said the desired destination was to open the file with Excel (which implies ODS CSV, ODS HTML or ODS TAGSETS.EXCELXP or ODS MSOFFICE2K), it seemed more likely that BREAK processing with BREAK or RBREAK statements would end up with the subtotal number being displayed in the correct table column, once the output was opened in Excel.

cynthia