topic Re: PROC REPORT line @ formatting in ODS and Base Reporting

PROC REPORT line @ formatting

TRJ — Wed, 17 Jun 2015 16:50:44 GMT

This is a continuation of a report that I previously received help with (https://communities.sas.com/message/277590), but since this a different issue, I decided to open another discussion. Xia Keshan helped me with the below code (although I added a couple of columns for demonstration purposes) to calculate row subtractions and it works well. The problem I'm having is formatting the computed variables to display nicely under the respective columns in the line @ statement. I've tried applying different commaW. widths, but the unless the subtractions results are the same size the alignment gets skewed. I've also tried to right justify the @line, but since each BREAK AFTER subtraction can have 1-4 digit differences, the results start off aligned, but get offset by different result widths. I appreciate any help. Thank you, Tim.

data have;

input country $ town $ _2012 : comma12. _2013 : comma12. _2014 : comma12. _2015 : comma12. ;

format _2012 _2013 _2014 _2015 : comma12.;

cards;

USA LA 999,999 985,733 888,888 777,777

USA NY 888,888 895,000 888,887 777,677

Canada VAN 500,000 550,000 650,000 675,000

Canada TOR 750,000 751,000 650,010 675,100

;

run;

proc report data=have nowd ;

column country town _2012 _2013 _2014 _2015;

define country/order;

define town/display;

define _2012/display;

define _2013/display;

define _2014/display;

define _2015/display;

compute before country;

n=0;

endcomp;

compute _2015;

n+1;

if n=1 then do;sum_2012=_2012;sum_2013=_2013;sum_2014=_2014;sum_2015=_2015;end;

else do;sum_2012+-1*_2012;sum_2013+-1*_2013;sum_2014+-1*_2014;sum_2015+-1*_2015; end;

endcomp;

compute after country;

line @1'Difference' @17 sum_2012 comma12. sum_2013 comma12. sum_2014 comma12. sum_2015 comma12.;

endcomp;

run;

Re: PROC REPORT line @ formatting

ballardw — Wed, 17 Jun 2015 19:40:50 GMT

Maybe this comes closer to the output you want?

data _null_;

/* the example data above isn't long enough for 'Difference' to appear in same column*/

length country $12.;

set have;

file print ods = (variables=(country town _2012 _2013 _2014 _2015));

format _2012 _2013 _2014 _2015 comma12.;

d2 = dif(_2012);

d3 = dif(_2013);

d4 = dif(_2014);

d5 = dif(_2015);

format d2 d3 d4 d5 comma12.;

if mod(_n_,2) = 1 then

put country town _2012 _2013 _2014 _2015;

else do;

country='';

put country town _2012 _2013 _2014 _2015;

put 'Difference' @3 d2 d3 d4 d5; /* with File ODS the column pointer refers to columns, not spaces*/

end;

run;

Re: PROC REPORT line @ formatting

Cynthia_sas — Thu, 18 Jun 2015 00:28:13 GMT

Hi: This is one reason NOT to use @ pointer control with PROC REPORT. There are other techniques you can use to change your summary line. See the code below. After you understand that the "EXTRABRK" variable is doing the "regular" summarizing and then the RBREAK numbers are being adjusted in the COMPUTE blocks for HEIGHT and WEIGHT -- then you can use NOPRINT to hide the EXTRABRK and the SOMENUMBER columns.

Cynthia

ods _all_ close; title; footnote;
options missing=' ';

data class;
length extrabrk name $20;
set sashelp.class;
if name = 'Alfred' then somenumber = 10;
else if name = 'Barbara' then somenumber = -5;
extrabrk = 'x';
run;

ods html file='c:\temp\changesum.html' ;
proc report data=class nowd;
   column extrabrk name somenumber age height weight sex;
   define extrabrk / order /* noprint */ ;
   define name / order;
   define somenumber / sum /* noprint */ ;
   break after extrabrk / summarize;
   rbreak after / summarize;
   ** put type of summary in name column;
   compute after extrabrk;
      name = 'Normal summary';
   endcomp;
   compute after;
      name = 'Adjusted numbers';
   endcomp;
   ** now adjust HEIGHT and WEIGHT and AGE on last summary line;
   compute height;
      if _break_ = '_RBREAK_' then height.sum = 1111;
   endcomp;
   compute weight;
      if _break_ = '_RBREAK_' then do;
         weight.sum = age.sum * 1000;
   ** change age.sum after using the original value for the weight column;
      age.sum = somenumber.sum;
   end;
   endcomp;
run;
ods _all_ close;

Re: PROC REPORT line @ formatting

Cynthia_sas — Thu, 18 Jun 2015 04:36:39 GMT

I would actually use a combo of DATA step and PROC REPORT "helper" columns to do this.

Cynthia

** make the 2nd row of each data "pair" negative by creating some extra variables;
data have;
length country $20;
input country $   town   $   _2012 : comma12.   _2013 : comma12. _2014 : comma12. _2015 : comma12. ;
array orig (4) _2012 _2013 _2014 _2015;
array subtr (4) d_2012 d_2013 d_2014 d_2015;
if mod(_n_,2) = 0 then do;
do i = 1 to 4 by 1;
    subtr(i) = orig(i) * -1;
end;
end;
else do;
do i = 1 to 4 by 1;
    subtr(i) = orig(i) ;
end;
end;
format _2012   _2013 _2014   _2015 d_2012   d_2013 d_2014   d_2015: comma12.;
cards;
USA LA 999,999 985,733 888,888 777,777
USA NY 888,888 895,000 888,887 777,677
Canada VAN 500,000 550,000 650,000 675,000
Canada TOR 750,000 751,000 650,010 675,100
;
run;

ods html file='c:\temp\alternate_method.html' style=htmlblue;
proc report data=have nowd
style(summary)=Header;
column country town _2012 _2013 _2014 _2015 d_2012 d_2013 d_2014 d_2015;
define country/order style(column)=Header;
define town/display style(column)=Header;
define _2012/sum ;
define _2013/sum ;
define _2014/sum ;
define _2015/sum ;
define d_2012/sum noprint;
define d_2013/sum noprint;
define d_2014/sum noprint;
define d_2015/sum noprint;
break after country / summarize;
compute after country;
** move the subtracted values into the other columns;
country='Difference';
_2012.sum = d_2012.sum;
_2013.sum = d_2013.sum;
_2014.sum = d_2014.sum;
_2015.sum = d_2015.sum;
endcomp;
run;
ods html close;

Re: PROC REPORT line @ formatting

Ksharp — Thu, 18 Jun 2015 14:20:43 GMT

Yeah. That is why need a lot of time to adjust its style .

Code: Program

data have;
input country  $   town   $   _2012 : comma12.   _2013 : comma12.  _2014 : comma12.  _2015 : comma12. ;
format _2012   _2013 _2014   _2015 : comma12.;
cards;
USA LA 999,999 985,733 888,888 777,777
USA NY 888,888 895,000 888,887 777,677
Canada VAN 500,000 550,000 650,000 675,000
  Canada TOR 750,000 751,000 650,010 675,100
;
run;
proc report data=have nowd ;
column country   town   _2012 _2013 _2014 _2015;
define country/order;
define town/display;
define _2012/display;
define _2013/display;
define _2014/display;
define _2015/display;
compute before country;
n=0;
endcomp;
compute _2015;
n+1;
if n=1 then do;sum_2012=_2012;sum_2013=_2013;sum_2014=_2014;sum_2015=_2015;end;
  else do;sum_2012+-1*_2012;sum_2013+-1*_2013;sum_2014+-1*_2014;sum_2015+-1*_2015; end;
endcomp;
compute after country;
if country='Canada' then do;a1=23; a2=34;a3=50;a4=64;end;
else if country='USA' then do;a1=23;a2=35;a3=52;a4=66;end;

line @1'Difference' @a1 sum_2012 comma8. @a2 sum_2013 comma8. @a3 sum_2014 comma8. @a4 sum_2015 comma8.;
endcomp;
run;

Xia Keshan

Re: PROC REPORT line @ formatting

TRJ — Thu, 20 Aug 2015 17:04:51 GMT

Thanks Cynthia,

I now have had the opportunity to use your solution quite a few times. It works very well. For those that may choose to use it also, given the usage of mod(_n_,2) to multiple every second record by -1 assumes that you have 2 matching records to subtract and if you don't, the signs in the difference line after that single/unmatched record get thrown off. This does work very well however.