## How do I sum select groups in PROC REPORT

Solved
Occasional Contributor
Posts: 14

# How do I sum select groups in PROC REPORT

Dear all,

I have this code:

PROC REPORT DATA=WORK.REPORT_DATA ;

COLUMNS Metricrank METRIC_LEVEL_2 Amounts ,SUMMARY_DATE ;

WHERE YEAR ne 2017;

DEFINE METRICRANK / GROUP FORMAT=Metricreorder. order=internal '';

DEFINE METRIC_LEVEL_2 / GROUP '';

DEFINE SUMMARY_DATE / across order=internal 'FY 2016' FORMAT=YYMMD7.;

DEFINE AMOUNTS / ANALYSIS SUM FORMAT=COMMA20.2 '';

Compute

COMPUTE AFTER METRICRANK;

LINE '';

ENDCOMP;

RUN;

which generates a report as such (without the Totals)

 Metricrank METRIC_LEVEL_2 31/01/2016 29/02/2016 31/03/2016 30/04/2016 31/05/2016 30/06/2016 31/07/2016 31/08/2016 30/09/2016 31/10/2016 30/11/2016 31/12/2016 31/01/2017 28/02/2017 31/03/2017 30/04/2017 31/05/2017 30/06/2017 31/07/2017 31/08/2017 1 Avg Debit Balances 1,933.19 1,928.39 1,909.33 1,980.59 1,977.09 1,992.60 1,986.33 1,977.66 1,956.16 2,016.96 2,024.24 2,041.70 2,019.76 1,929.44 1,934.39 1,933.58 1,914.24 1,898.88 1,887.41 1,862.88 RWA 1,861.07 1,868.95 1,851.91 1,897.52 1,891.22 1,935.29 1,925.81 1,925.42 1,914.89 1,989.04 1,716.48 1,716.35 1,681.40 1,606.18 1,599.92 1,614.26 1,565.14 1,532.58 1,503.88 2 Commission / Fees 0.05 0.04 0.08 0.14 0.06 0.09 0.06 0.07 0.10 0.14 0.06 0.06 0.07 0.10 0.12 0.09 0.11 0.07 0.09 0.09 Fund Transfer Pricing -1.10 -1.03 -1.07 -1.06 -1.09 -1.06 -1.09 -1.09 -1.04 -1.11 -1.08 -1.13 -1.12 -1.10 -1.09 -1.05 -1.09 -1.05 -1.08 -1.08 Income 3.37 3.35 2.98 3.95 3.62 3.37 3.80 3.67 3.16 4.64 3.61 3.46 3.40 3.88 3.22 3.32 3.29 -0.04 0.05 -0.88 Net Interest Income 0.16 0.15 0.18 0.22 0.24 0.22 0.22 0.21 0.19 0.22 0.21 0.22 0.22 0.19 0.23 0.24 0.24 3.89 4.42 4.72 Total: 2.49 2.52 2.16 3.25 2.83 2.61 2.99 2.86 2.40 3.90 2.81 2.61 2.57 3.07 2.47 2.61 2.55 2.87 3.48 2.86 3 Allocated Cost -1.05 -1.14 -0.95 -1.22 -1.23 -1.31 -1.22 -0.77 -1.54 -0.76 -0.75 -1.57 -1.19 -0.87 -0.99 -1.36 -1.31 -1.09 -0.60 Client Variable Cost -0.48 -1.01 -0.73 -0.55 -0.63 -0.52 -0.61 -0.53 -0.54 -0.70 -0.58 -0.61 -0.77 -0.83 -0.70 -0.67 -0.65 -0.75 -0.71 -0.58 Portfolio Variable Cost -0.07 -1.27 -0.69 -0.64 -0.69 -0.77 -0.42 -0.79 -0.25 -1.28 -1.10 -0.99 -0.63 -0.57 -0.88 -0.64 -0.26 -0.36 -1.11 -0.13 Total: -1.60 -3.41 -2.36 -2.41 -2.55 -2.60 -2.25 -2.09 -2.33 -2.74 -2.43 -3.17 -2.59 -2.28 -2.57 -2.68 -2.23 -2.20 -2.42 -0.71

As you can see, I would like to summarize only Metricrank 2 and 3 to get a net figure.  Is this possible?  I was trying Compute with if statements but wasn't successful.

Regards,

Aksel

Accepted Solutions
Solution
‎09-22-2017 10:58 AM
SAS Super FREQ
Posts: 824

## Re: How do I sum select groups in PROC REPORT

You can do this with Proc REPORT, see example below. It uses a computed column as the last column. From this compute block you can control all the column values left of this column. Within a compute block, you have the _BREAK_ variable, that tells whether you are in a break line and then set the values accordingly. The _dummy column is printed to understand what values you have available. You can set it to NOPRINT to suppress printing, the columns created by the across, need to be accessed by their column number like _Cn_.

SampleCode:

``````data forReport;
set sashelp.cars;
where make in ("Audi", "Volvo", "Volkswagen");
select (make);
when ("Audi") mediRank = 1;
when ("Volkswagen") mediRank = 2;
when ("Volvo") mediRank = 3;
otherwise;
end;

keep make driveTrain type Horsepower mediRank;
run;

proc report data=forReport;
column mediRank type driveTrain, horsePower _dummy;
define mediRank / group;
define type / group;
define driveTrain / across;
define horsePower / analysis;
define _dummy / computed;
break after mediRank / summarize;

compute _dummy / char length=32;
_dummy = catx(":", mediRank, _break_);
if lowcase(_break_) = "medirank" and mediRank ne 1 then do;
type = "Total";
end;

if lowcase(_break_) = "medirank" and mediRank = 1 then do;
call missing(mediRank, type);
call missing(_c3_, _c4_);
end;
endcomp;
run;``````

All Replies
Solution
‎09-22-2017 10:58 AM
SAS Super FREQ
Posts: 824

## Re: How do I sum select groups in PROC REPORT

You can do this with Proc REPORT, see example below. It uses a computed column as the last column. From this compute block you can control all the column values left of this column. Within a compute block, you have the _BREAK_ variable, that tells whether you are in a break line and then set the values accordingly. The _dummy column is printed to understand what values you have available. You can set it to NOPRINT to suppress printing, the columns created by the across, need to be accessed by their column number like _Cn_.

SampleCode:

``````data forReport;
set sashelp.cars;
where make in ("Audi", "Volvo", "Volkswagen");
select (make);
when ("Audi") mediRank = 1;
when ("Volkswagen") mediRank = 2;
when ("Volvo") mediRank = 3;
otherwise;
end;

keep make driveTrain type Horsepower mediRank;
run;

proc report data=forReport;
column mediRank type driveTrain, horsePower _dummy;
define mediRank / group;
define type / group;
define driveTrain / across;
define horsePower / analysis;
define _dummy / computed;
break after mediRank / summarize;

compute _dummy / char length=32;
_dummy = catx(":", mediRank, _break_);
if lowcase(_break_) = "medirank" and mediRank ne 1 then do;
type = "Total";
end;

if lowcase(_break_) = "medirank" and mediRank = 1 then do;
call missing(mediRank, type);
call missing(_c3_, _c4_);
end;
endcomp;
run;``````
Occasional Contributor
Posts: 14

## Re: How do I sum select groups in PROC REPORT

Thank you very much!!! Worked like a charm.
Occasional Contributor
Posts: 14

## Re: How do I sum select groups in PROC REPORT

Just a quick follow up question:

Is it possible to do calculations based on rows/columns?  ie. Can I subtract the total from one specific line from another line? Now that I know how to assign dummy values to identify the rows to display data, I was wondering if it was possible to do calculations.

I have been trying to find some articles regarding this, but it seems like it would be easier to do it at the data gathering stage rather than reporting.

Regards,

Aksel

SAS Super FREQ
Posts: 824

## Re: How do I sum select groups in PROC REPORT

You get a lot of sample programs with your SAS installation, one of this sample programs is called rpt61.sas it is located here ...\SASFoundation\9.4\core\sample, it has some very nice xamples for calculated stuff, the programs dates back to 1990 and was made for listing output, but the calculations are very helpful.
Occasional Contributor
Posts: 14

Aksel

SAS Super FREQ
Posts: 824

## Re: How do I sum select groups in PROC REPORT

Find attached the code of the rpt61.sas

Occasional Contributor
Posts: 14