topic Re: Doing row subtractions in PROC REPORT in ODS and Base Reporting

Doing row subtractions in PROC REPORT

TRJ — Wed, 10 Jun 2015 15:54:35 GMT

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.

Re: Doing row subtractions in PROC REPORT

ballardw — Wed, 10 Jun 2015 20:07:40 GMT

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.

Re: Doing row subtractions in PROC REPORT

TRJ — Wed, 10 Jun 2015 21:13:02 GMT

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;

Re: Doing row subtractions in PROC REPORT

Ksharp — Thu, 11 Jun 2015 12:54:01 GMT

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

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

Re: Doing row subtractions in PROC REPORT

TRJ — Thu, 11 Jun 2015 18:38:08 GMT

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.

Re: Doing row subtractions in PROC REPORT

Ksharp — Fri, 12 Jun 2015 13:04:51 GMT

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