## Doing row subtractions in PROC REPORT

Solved
Occasional Contributor
Posts: 6

# Doing row subtractions in PROC REPORT

I imagine this should be easy, but I can't find a solution. All I would like to do is use BREAK AFTER and subtract some rows instead of SUMMARIZE?

What I would like the report to look like:

country     town         2012          2013

USA          LA          999,999     985,733

NY          888,888     895,000

Difference              111,111      90,733

TOR       750,000      751,000

Difference            -250,000     -201,000

Code to summarize is straightforward, but I can't seem to find an example of subtraction using PROC REPORT and maybe this is the wrong procedure for this report. I really appreciate any help. Thank you, Tim.

Accepted Solutions
Solution
‎06-11-2015 08:54 AM
Super User
Posts: 10,788

## Re: Doing row subtractions in PROC REPORT

No. You are not in wrong way .PROC REPORT can handle this .

### Code: Program

`data have;input country  \$   town   \$   _2012 : comma12.   _2013 : comma12. ;format _2012   _2013 : comma12.;cards;USA LA 999,999 985,733 USA NY 888,888 895,000Canada VAN 500,000 550,000  Canada TOR 750,000 751,000;run;proc report data=have nowd ;column country   town   _2012 _2013;define country/order;define town/display;define _2012/display;define _2013/display;compute before country; n=0;endcomp;compute _2013; n+1; if n=1 then do;sum_2012=_2012;sum_2013=_2013;end;  else do;sum_2012+-1*_2012;sum_2013+-1*_2013; end;endcomp;compute after country; line @1'Difference' @25 sum_2012 comma12. sum_2013 comma12.;endcomp;run;`

### Results: Program

 Canada VAN 500,000 550,000 TOR 750,000 751,000 Difference                  -250,000    -201,000 USA LA 999,999 985,733 NY 888,888 895,000 Difference                   111,111      90,733

Xia Keshan

All Replies
Super User
Posts: 13,583

## Re: Doing row subtractions in PROC REPORT

It will help to show 1) some starting data, 2) desired output and 3) code of what you have tried so far.

Likely you want a compute block for after country.

I would also ask if the sign is actually critical. I believe default behavior for Proc Report would place TOR before VAN.

The statistic for the summary could be RANGE, which would have the added advantage of working with 3 or more towns within country.

Occasional Contributor
Posts: 6

## Re: Doing row subtractions in PROC REPORT

Thanks for the reply. The example was really only for illustrative purposes as the code is a little involved. I also work whith highly sensitive data, so my apologies for the spoofing. I had to do a little more spoofing below, but the code and data are close. The code below only SUMMARIZES as I was using old code. I was hoping there was a more eloquent way to find the row differences other than generating a "DIFF" column for every period then transposing those DIFF columns to rows. I've relied quite heavily on PROC REPORT over the years so if this is the wrong procedure for doing subtractions, please point me in the right direction? Thanks for taking the time to help me out, Tim.

Data before PROC REPORT step ( COMPARE_PS_RPT)

 1 System1 Table1 source1 1991 2917 2199 1971 1633 1857 2177 1932 1817 1215 1931 1869 1942 2 System1 Table1 source2 1212 1312 1323 1203 861 1100 1173 1101 995 631 1090 1008 1003 3 System1 Table2 source1 1449 1557 1645 1563 1358 1368 1568 1481 1466 1124 1532 1509 1744 4 System1 Table2 source2 1052 1148 1365 1060 1066 1047 1169 1094 1096 825 1152 1096 1459 5 System2 Table3 source1 320 335 331 310 279 312 337 310 383 286 356 339 336 6 System2 Table3 source2 626 663 608 608 517 631 679 591 608 350 559 531 737

PROC REPORT data=COMPARE_PS_RPT split='~'  nowindows contents='' center  style(HEADER)={background=black foreground=white font_weight=bold};

column SYSTEM TABLE TYPE period01 period02 period03 period04 period05 period06
period07 period08 period09 period10 period11 period12 period13 ;
define SYSTEM / group 'System' order ;
define TABLE / group 'Table Space' order order=internal ;
define TYPE / 'Source';
define PERIOD01 / 'Period 01' FORMAT=COMMA9.;
define PERIOD02 / 'Period 02' FORMAT=COMMA9.;
define PERIOD03 / 'Period 03' FORMAT=COMMA9.;
define PERIOD04 / 'Period 04' FORMAT=COMMA9.;
define PERIOD05 / 'Period 05' FORMAT=COMMA9.;
define PERIOD06 / 'Period 06' FORMAT=COMMA9.;
define PERIOD07 / 'Period 07' FORMAT=COMMA9.;
define PERIOD08 / 'Period 08' FORMAT=COMMA9.;
define PERIOD09 / 'Period 09' FORMAT=COMMA9.;
define PERIOD10 / 'Period 10' FORMAT=COMMA9.;
define PERIOD11 / 'Period 11' FORMAT=COMMA9.;
define PERIOD12 / 'Period 12' FORMAT=COMMA9.;
define PERIOD13 / 'Period 13' FORMAT=COMMA9.;

break after TABLE /  summarize  style={background=whitesmoke foreground=black font_weight=bold};

compute after TABLE / style(LINES)={background=whitesmoke foreground=black font_weight=bold};

lr = SYSTEM;

SYSTEM = " ";
SYSTEM = "Total";

endcomp;

break after SYSTEM / summarize style={background=whitesmoke foreground=black font_weight=bold};

compute after SYSTEM / style(LINES)={background=whitesmoke foreground=black font_weight=bold};

lr = SYSTEM;

ha = " ";
ha = "SYSTEM Total";

endcomp;

RUN;

Solution
‎06-11-2015 08:54 AM
Super User
Posts: 10,788

## Re: Doing row subtractions in PROC REPORT

No. You are not in wrong way .PROC REPORT can handle this .

### Code: Program

`data have;input country  \$   town   \$   _2012 : comma12.   _2013 : comma12. ;format _2012   _2013 : comma12.;cards;USA LA 999,999 985,733 USA NY 888,888 895,000Canada VAN 500,000 550,000  Canada TOR 750,000 751,000;run;proc report data=have nowd ;column country   town   _2012 _2013;define country/order;define town/display;define _2012/display;define _2013/display;compute before country; n=0;endcomp;compute _2013; n+1; if n=1 then do;sum_2012=_2012;sum_2013=_2013;end;  else do;sum_2012+-1*_2012;sum_2013+-1*_2013; end;endcomp;compute after country; line @1'Difference' @25 sum_2012 comma12. sum_2013 comma12.;endcomp;run;`

### Results: Program

 Canada VAN 500,000 550,000 TOR 750,000 751,000 Difference                  -250,000    -201,000 USA LA 999,999 985,733 NY 888,888 895,000 Difference                   111,111      90,733

Xia Keshan

Occasional Contributor
Posts: 6

## Re: Doing row subtractions in PROC REPORT

Thanks Xia,

That's brilliant and I would not of thought of that approach. I've added it to my code and it works well. To close the loop for those folks following along, I was getting uninitialized variable warnings on the COMPUTE statements because I did not have DISPLAY in the DEFINE statements for those variables. Also, I need to do some work on the LINE @1 statement to pretty up the report so the "difference" values line up nicely below the respective columns. Thanks to all for the help, Tim.

Super User
Posts: 10,788

## Re: Doing row subtractions in PROC REPORT

If you don't have DISPLAY usage variable, Maybe it is  ANALYSIS usage .

If so .  Change _2012 into _2012.sum    and      _2013 into _2013.sum

🔒 This topic is solved and locked.