topic Re: How to sum a column variable from proc report in ODS and Base Reporting

How to sum a column variable from proc report

SasGuy614 — Tue, 13 Oct 2015 16:30:51 GMT

Hello - I seached for documentation on how to properly create the sum of a variable using the RBREAK function. I can't seem to find the right syntax. All I want to do is show a summation for the column titled "CurrBal". Can someone show/point me to documentation that shows how to properly do so.

Thanks for the guidiance.

/*CODE BELOW*/

proc report
   data = CML.Final_&ReportDate. nowd missing;
   where Product_Name like '%HYBRID%';
   Column
            OpenDateDT Acct CustomerName CurrBal Product_Name
           Org_Level_02 Org_Level_03 Org_Level_04 Org_Level_05 Org_Level_06;
   label
           OpenDateDT = 'Open Date';
   DEFINE Acct           / STYLE(COLUMN)=[CELLWIDTH=115PX TEXTALIGN=r tagattr="format:@"];
     DEFINE OpenDateDT     / STYLE(COLUMN)=[CELLWIDTH=125PX TEXTALIGN=l tagattr='type:DateTime format:mm/dd/yyyy'];
   DEFINE Product_Name   / STYLE(COLUMN)=[CELLWIDTH=250PX TEXTALIGN=l];
   DEFINE CustomerName   / STYLE(COLUMN)=[CELLWIDTH=400PX TEXTALIGN=l];
   DEFINE Org_Level_02   / STYLE(COLUMN)=[CELLWIDTH=250PX TEXTALIGN=l];
   DEFINE Org_Level_03   / STYLE(COLUMN)=[CELLWIDTH=400PX TEXTALIGN=l];
   DEFINE Org_Level_04   / STYLE(COLUMN)=[CELLWIDTH=400PX TEXTALIGN=l];
   DEFINE Org_Level_05   / STYLE(COLUMN)=[CELLWIDTH=500PX TEXTALIGN=l];
   DEFINE Org_Level_06   / STYLE(COLUMN)=[CELLWIDTH=400PX TEXTALIGN=l];
run;

Balance
$50,000,000.00
$32,515,368.16
$18,003,846.79
$15,007,005.27
$14,742,712.88
$10,000,890.41
$5,002,123.52
$2,459,806.99
$1,502,804.41
$1,005,426.95
$1,000,816.70
$603,445.59
$0.00
$151,844,247.67 (missing summation value that I want the report to calculate)

Re: How to sum a column variable from proc report

ballardw — Tue, 13 Oct 2015 21:52:34 GMT

Have you tried:

Rbreak after / ol summarize;

Re: How to sum a column variable from proc report

SasGuy614 — Thu, 15 Oct 2015 19:28:42 GMT

Thanks for the suggestion, To answer your question, yes I have. The problem is that the summarized line is also providing a summary line for Date and Interest Rate, my guess is because they are also numeric variables. I only want to show a summarized line for CurrBal. How do I use this function but not have it included summary lines for Date and Interest Rate? Also the summary line isn't well defined when it's used it as part of an Excel ODS. Is there a way to make the total calucation bold?

Thanks!

proc report
   data = CML.Final_&ReportDate. nowd missing;
   where Product_Name like '%NOTICE%';
   Column
           OpenDateDT Acct CustomerName CurrBal InterestRate Product_Name
           Org_Level_02 Org_Level_03 Org_Level_04 Org_Level_05 Org_Level_06;
   label
           OpenDateDT = 'Open Date';
   DEFINE Acct           / STYLE(COLUMN)=[CELLWIDTH=115PX TEXTALIGN=r tagattr="format:@"];
     DEFINE OpenDateDT     / STYLE(COLUMN)=[CELLWIDTH=125PX TEXTALIGN=l tagattr='type:DateTime format:mm/dd/yyyy'];
   DEFINE CurrBal           / sum;
   DEFINE InterestRate   / STYLE(COLUMN)=[CELLWIDTH=125PX TEXTALIGN=r tagattr='format:.0%'];
   DEFINE Product_Name   / STYLE(COLUMN)=[CELLWIDTH=250PX TEXTALIGN=l];
   DEFINE CustomerName   / STYLE(COLUMN)=[CELLWIDTH=375PX TEXTALIGN=l];
   DEFINE Org_Level_02   / STYLE(COLUMN)=[CELLWIDTH=250PX TEXTALIGN=l];
   DEFINE Org_Level_03   / STYLE(COLUMN)=[CELLWIDTH=400PX TEXTALIGN=l];
   DEFINE Org_Level_04   / STYLE(COLUMN)=[CELLWIDTH=400PX TEXTALIGN=l];
   DEFINE Org_Level_05   / STYLE(COLUMN)=[CELLWIDTH=500PX TEXTALIGN=l];
   DEFINE Org_Level_06   / STYLE(COLUMN)=[CELLWIDTH=400PX TEXTALIGN=l];
   Rbreak after / summarize dol dul;
   title 'NOTICE REPORT';
run;

Re: How to sum a column variable from proc report

Cynthia_sas — Thu, 15 Oct 2015 21:49:37 GMT

Hi: All numeric variables are given a USAGE of ANALYSIS with statistic of SUM by default. If, for example, I was using SASHELP.CLASS and I did not want a sum for the AGE variable (which is meaningless) or for a DATE variable, then I need to code a different USAGE:
define AGE / display 'Age';
define DATEVAR / display 'Date';

Then, when the SUMMARIZE is being done, those 2 variables will be excluded (not shown) on the summary line from a BREAK or RBREAK.

cynthia

Re: How to sum a column variable from proc report

SasGuy614 — Thu, 15 Oct 2015 23:49:54 GMT

Thanks for the reply. I'm not quite following your suggestion. The line below is blue is the only summary line that I want to show. The two in red are the ones showing now that I don't want displayed. I haven't been able a way to drop the two summaries that I don't need.

proc report
   data = CML.Final_&ReportDate. nowd missing;
   where Product_Name like '%NOTICE%';
   Column
           OpenDateDT Acct CustomerName CurrBal InterestRate Product_Name
           Org_Level_02 Org_Level_03 Org_Level_04 Org_Level_05 Org_Level_06;
   label
           OpenDateDT = 'Open Date';
   DEFINE Acct           / STYLE(COLUMN)=[CELLWIDTH=115PX TEXTALIGN=r tagattr="format:@"];
     DEFINE OpenDateDT     / STYLE(COLUMN)=[CELLWIDTH=125PX TEXTALIGN=l tagattr='type:DateTime format:mm/dd/yyyy'];
   DEFINE CurrBal           / sum;
   DEFINE InterestRate   / STYLE(COLUMN)=[CELLWIDTH=125PX TEXTALIGN=r tagattr='format:.0%'];
   DEFINE Product_Name   / STYLE(COLUMN)=[CELLWIDTH=250PX TEXTALIGN=l];
   DEFINE CustomerName   / STYLE(COLUMN)=[CELLWIDTH=375PX TEXTALIGN=l];
   DEFINE Org_Level_02   / STYLE(COLUMN)=[CELLWIDTH=250PX TEXTALIGN=l];
   DEFINE Org_Level_03   / STYLE(COLUMN)=[CELLWIDTH=400PX TEXTALIGN=l];
   DEFINE Org_Level_04   / STYLE(COLUMN)=[CELLWIDTH=400PX TEXTALIGN=l];
   DEFINE Org_Level_05   / STYLE(COLUMN)=[CELLWIDTH=500PX TEXTALIGN=l];
   DEFINE Org_Level_06   / STYLE(COLUMN)=[CELLWIDTH=400PX TEXTALIGN=l];
   rbreak after / summarize dol dul;
   title 'NOTICE REPORT';
run;

Re: How to sum a column variable from proc report

Cynthia_sas — Fri, 16 Oct 2015 02:32:34 GMT

...And, as I suggested, you need to add the usage option DISPLAY after the slash in each statement that you show in RED.

Right now, because the date and the rate variables are numeric, they are being summed by DEFAULT; because SUM is the DEFAULT usage.

You are NOT taking control of usage, so you are getting a DEFAULT usage for your character and numeric variables. What is interesting to me is that you read enough to put SUM (the default) on the DEFINE statement for CurrBal when you did NOT need to, but didn't investigate enough to understand that SUM is the default for all numeric variables.

You didn't need SUM for CurrBal. That was going to happen anyway. What you need to do is REMOVE the default from the OTHER variables. Providing a specific usage of DISPLAY for the other 2 variables will do that.

I am not at a computer where I can post code easily, I don't really want to retype the whole statement because I can't format it...

DEFINE variable_name / DISPLAY ..... ;

What I have in my example that you don't have is the usage option DISPLAY.

cynthia

putting "SUM" on the Cu

Re: How to sum a column variable from proc report

SasGuy614 — Fri, 16 Oct 2015 13:54:58 GMT

Thank you for the insight. I did some addiitonal research and the code is working as expected.