How do I sum select groups in PROC REPORT

[asdf12_12]

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

[BrunoMueller · Accepted solution]

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;

[asdf12_12]

Thank you very much!!! Worked like a charm.

[asdf12_12]

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

[BrunoMueller]

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.

[asdf12_12]

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

[BrunoMueller]

Find attached the code of the rpt61.sas

[asdf12_12]

Oh wow! Thank you! You have gone out of your way to sort my troubles. Thank you very much for going the extra distance..