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.
Your help is much appreciated.
Regards,
Aksel
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;
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;
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
Thank you very much for your reply and apologies for my delayed reply. I am trying to get the business to give me access to the folder. Mean time, I will search and see if I can download it from another place.
Thanks again for your help.
Aksel
Find attached the code of the rpt61.sas
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: