The SAS Output Delivery System and reporting techniques

Doing row subtractions in PROC REPORT

Accepted Solution Solved
Reply
Occasional Contributor TRJ
Occasional Contributor
Posts: 6
Accepted Solution

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

Canada     VAN       500,000      550,000

                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: 9,682

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,000
Canada 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

CanadaVAN500,000550,000
TOR750,000751,000
Difference                  -250,000    -201,000
USALA999,999985,733
NY888,888895,000
Difference                   111,111      90,733

Xia Keshan

View solution in original post


All Replies
Super User
Posts: 10,500

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 TRJ
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) 

1System1Table1source11991291721991971163318572177193218171215193118691942
2System1Table1source21212131213231203861110011731101995631109010081003
3System1Table2source11449155716451563135813681568148114661124153215091744
4System1Table2source2105211481365106010661047116910941096825115210961459
5System2Table3source1320335331310279312337310383286356339336
6System2Table3source2626663608608517631679591608350559531737

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: 9,682

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,000
Canada 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

CanadaVAN500,000550,000
TOR750,000751,000
Difference                  -250,000    -201,000
USALA999,999985,733
NY888,888895,000
Difference                   111,111      90,733

Xia Keshan

Occasional Contributor TRJ
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: 9,682

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 734 views
  • 0 likes
  • 3 in conversation