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.
No. You are not in wrong way .PROC REPORT can handle this .
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;
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
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.
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;
No. You are not in wrong way .PROC REPORT can handle this .
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;
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
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.
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
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 25. 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.