SUMMARIZE control in a RBREAK statement while exporting to Excel

[pawan]

Hi,

I am trying to export to an excel sheet using ODS for PROC REPORT. The COLUMNS statement has got variable with two stats to be delivered out 'sum' and 'pctsum'. As, I use a RBREAK statement the Excel sheet generate summarize for 'sum' and 'pctsum'. Can I control the SUMMARIZE only for SUM variable rather than for all other numeric variables.

Ex:
libanme xl excel '....\book.xls ' ;

proc report data=sashelp.retail nowd out=xl.sheet;

columns date year sales,(sum pctsum) ;

define date / group;

define year / group;

define sum/ 'TOTAL SALES';

define pctsum / 'GROWTH IN %';

RBREAK after date / suppress ol skip summarize;

*can i have this summary only for TOTAL SALES and not for GROWTH IN %;

run;

NOTE: The values are been able to control in the Results Window, but couldnt be controlled in the output Excel Sheet as it is generating SUMMARIZE for all the numeric variables.

Any support will be appreciated.

Thanks in advance

pwN.

[Cynthia_sas]

Hi:

  I'm not sure what you mean when you say "The values are been able to control in the Results Window, but couldnt be controlled in the output Excel Sheet." When I run code similar to your code, with a corrected version of the RBREAK statement, I see a total for the Growth in % in the Results Window. If you have managed to suppress a total for the PCTSUM item, you did not show that code in your posting.

  If you run code with RBREAK AFTER DATE, you should see an ERROR message in the log:

680  RBREAK after date/ suppress ol skip summarize;

                  ----

                  79

                  202

ERROR 79-322: Expecting a /.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

So, either you meant to type

RBREAK AFTER/ suppress ol skip summarize; /* for grand total summary row */

or you meant to type

BREAK AFTER DATE/suppress ol skip summarize;  /* for a summary row after each unique value for DATE */

or you meant to type

BREAK AFTER YEAR /suppress ol skip summarize;  /* for a summary row after each unique value for YEAR*/

If you have suppressed either a break group total or a report break total in the LISTING destination (Output Window), what you see should be roughly equivalent to what is placed in the OUT= dataset (in this case, the Excel sheet). However, the fact is that when you create a dataset with OUT=, you will see output that is very close, but not exactly the same as what you see in the LISTING window or Results Viewer window. For example, in the Results Viewer, you NEVER see _BREAK_ (which is created behind the scenes by PROC REPORT), but you will ALWAYS see an _BREAK_ variable in the output dataset, unless you choose to drop this automatic variable. As another example, in your code, you might use NOPRINT on an item, such as YEAR, however, while the display of YEAR would be suppressed in the Results Viewer, you would still see year in the OUT= file. Then, there's the fact that you may see the DEFINE statement label in the Results Viewer, but in the OUT= file, you may see an absolute column number, such as _C3_ or _C4_. In addition, you should see the OL and SKIP in evidence in the Output Window for LISTING results, but you would not see evidence of the OL or the SKIP in the OUT= file.

For more help with what you want to do, you might want to work with Tech Support on this question.

cynthia

[pawan]

Yepp Cynthia, Sorry. I should have mentioned BREAK and not RBREAK statement. Anyways, my requirement is to generate summary only for sales.sum variable and omit summary for sales.pctsum variable. Can I expect any solution for this.

Thanks in advance

[Cynthia_sas]

Hi:

  Is there a reason why you use OUT= and the SAS Libname engine for Excel?  Can you use ODS for this output -- which seems more like report output than data output, since you are adding summary lines to the original data. You said "NOTE: The values are been able to control in the Results Window" -- this implied to me that you found (but did not post) a technique for blanking out SALES.PCTSUM in the LISTING window. Is this the case? If so, then you already have a solution.

  Since you said you had been able to control what you wanted in the Results Window, I assumed that you had tried the usual way to "blank out" or change the appearance of the summary line generated by a BREAK or RBREAK statement is to use a COMPUTE block:

COMPUTE AFTER;

    sales.pctsum = .;

ENDCOMP;

  With this technique, the value on the RBREAK summary line for SALES.PCTSUM would be "reset" to missing. Then if you had the MISSING option set to display missing as blank, you would not display the value for SALES.PCTSUM on the summary row. When I use the above compute block, whether with the LIBNAME engine to Excel or with ODS, I find that I am able to blank out the cell for SALES.PCTSUM on the RBREAK row. See the attached screen shots. I find I like the ODS technique better than the LIBNAME technique. With the ODS technique, I have more control over things like the column headers and the column sizes when the output is opened in Excel. With the LIBNAME technique, the default for SUM and PCTSUM is for PROC REPORT to send Excel the "internal" column names of _C3_ and _C4_ and you lose the headers that you set in PROC REPORT.

  If you need more in-depth analysis of why your technique does not work as expected, you would have to post the code that controlled the values in the Results Window, so folks could comment. With the code that you originally posted, however, the SALES.PCTSUM summary information will be written to the Excel sheet named in the OUT= option. And, since you say the code you posted should have used BREAK instead of RBREAK, I don't understand why you would have a BREAK on DATE with DATE being a GROUP variable. It makes more sense to me that you might have a BREAK line generated for each YEAR, not each DATE. If your original code was corrected to BREAK AFTER DATE, then you'd be generating a summary line after every unique value of DATE (as shown in this partial LISTING output):

                   Retail sales in millions of $
                                    GROWTH
  DATE       YEAR   TOTAL SALES       IN %
  80Q1       1980          $220     3.46%
                   ------------  ---------

                           $220     3.46%

  80Q2       1980          $257     4.05%
                   ------------  ---------

                           $257     4.05%

  80Q3       1980          $258     4.06%
                   ------------  ---------

                           $258     4.06%

  80Q4       1980          $295     4.64%
                   ------------  ---------
                           $295     4.64%

  81Q1       1981          $247     3.89%
                   ------------  ---------
                           $247     3.89%

   As you see, working with the code you posted, a BREAK on DATE doesn't make much sense. There must be other code you have tried, which you did not post. So, if you do have an alternate technique or alternate code, other than what you posted, and you want to continue to debug your technique and your code, then you might consider working with Tech Support on this issue.

cynthia

[Ksharp]

Then you should try to use ' line ' statement of proc report.

Ksharp

[Cynthia_sas]

Hi:

  Since the OP wants to use the LIBNAME engine with OUT=, the LINE statement approach won't work in this case for several reasons:

1) LINE statement output is NOT written to the OUT= dataset.

And,

2) LINE statement will only position the totals in a specific print position in the LISTING window, which, is not the desired destination in this case, so there is no guarantee that the LINE statement would position the total for SALES.SUM correctly

And,

3) there are other ways to do what the OP wants, but not with the LIBNAME engine.

cynthia

[pawan]

Thanks Cynthia, that worked for me..

I really didnt notice for column headers, I m surprised when I did.. Thanks for ODS suggestion. Hope this works for me. Unfortunately, my client is using SAS V8. Is there any chance to generate such report using V8 coding, especially an EXCEL output?

[Cynthia_sas]

Hi:

  When you create an output dataset using any SAS procedure, you are likely to get differences -- report procedures allow you to do things like change column headers and have titles, change column fonts, etc. Datasets don't have titles or colors or fonts.

   TAGSETS.EXCELXP was introduced using features of PROC TEMPLATE in SAS 9, it won't work in SAS 8. So, if you cannot use TAGSETS.EXCELXP for your client, then you will have to use ODS HTML probably with STYLE=MINIMAL. The thing is that in SAS 8, ODS HTML created HTML 3.2 compliant HTML tags -- which Excel is happy to open and render. In SAS 9, ODS HTML creates HTML 4.0 compliant tags -- which Excel is happy to open, but Excel didn't like the HTML 4.0 in-line STYLE section -- so Microsoft invented their own "flavor" of HTML. If you needed HTML in SAS 9, I'd recommend using ODS MSOFFICE2K destination -- which generates Microsoft "flavor" of HTML.

  PROC REPORT is a very powerful reporting procedure and ODS HTML will still get your output into a form that can be opened in Excel. ODS HTML doesn't have as many of the features as ODS TAGSETS.EXCELXP, but it will do if all you have is V8.

cynthia