Help using Base SAS procedures

Proc Report - how to highlight Columns & add a text to the total row

Reply
Frequent Contributor
Posts: 134

Proc Report - how to highlight Columns & add a text to the total row

Hi, I've asked a similar question before but this one is slightly different...

I am using Proc Report to generate a report...  the only issue is that when I am displaying "target" values in the rows I want to show it as an average but in the total I want to show a sum of all the average rows... It's confusing to explain that's why I've attached a snapshot

In the attached snapshot, total target for North America should be 115 and that of South America should be 200



Here is the code that I've used:

data someData2;

 

  infile cards dsd dlm="," missover;

  input

    countryGroup : $32.

    Country : $32.

    target : 8.

    Day : 8.

    volume : 8.

  ;

  cards;

NorthAmerica,Canada,100,1,10

NorthAmerica,Canada,100,2,120

NorthAmerica,Canada,100,3,75

NorthAmerica,USA,100,1,110

NorthAmerica,USA,10,2,20

NorthAmerica,USA,5,3,25

NorthAmerica,USA,5,4,15

NorthAmerica,USA,5,5,5

NorthAmerica,USA,5,6,5

NorthAmerica,USA,5,7,1

NorthAmerica,USA,5,8,7

NorthAmerica,USA,5,9,25

NorthAmerica,USA,5,10,65

SouthAmerica,Brazil,75,1,10

SouthAmerica,Brazil,75,2,50

SouthAmerica,Paraguay,100,1,10

SouthAmerica,Paraguay,50,2,10

SouthAmerica,Peru,50,1,17

SouthAmerica,Peru,50,2,10

SouthAmerica,Peru,50,3,100

;

run;

 

options missing=0;

ods html file='c:\absolute_cols_1.html' style=htmlblue;

  

proc report data= someData2 nowd

     style(column)={font_face=times font_size=8pt}

     style(header)={just=center font_face=arial};

title 'Report';

column CountryGroup Country target (volume,day);

define countryGroup / group 'Group '

        style(column)=[background=gray foreground=white

                       font_weight=bold];

define country / order=formatted group ' '

        style(column)=[background=gray foreground=white

                       font_weight=bold];

define target / analysis mean;

define day / across 'Day ' ORDER=INTERNAL;

define volume / analysis sum format=comma10.  ' ';

compute volume;

    ** remove all possibility that the issue is with the ARRAY;

    ** statement or the VNAME function;

    if missing(_break_) then do;

        if _c4_ > target.mean then

          call define('_c4_', "style", "style={background=cx997086}");

        if _c5_ > target.mean then

          call define('_c5_', "style", "style={background=cx997086}");

        if _c6_ > target.mean then

          call define('_c6_', "style", "style={background=cx997086}");

        if _c7_ > target.mean then

          call define('_c7_', "style", "style={background=cx997086}");

        if _c8_ > target.mean then

          call define('_c8_', "style", "style={background=cx997086}");

        if _c9_ > target.mean then

          call define('_c9_', "style", "style={background=cx997086}");

        if _c10_ > target.mean then

          call define('_c10_', "style", "style={background=cx997086}");

        if _c11_ > target.mean then

          call define('_c11_', "style", "style={background=cx997086}");

        if _c12_ > target.mean then

          call define('_c12_', "style", "style={background=cx997086}");

        if _c13_ > target.mean then

          call define('_c13_', "style", "style={background=cx997086}");

     end;

   endcomp;

  compute after CountryGroup;

      CountryGroup = catx(" ", CountryGroup, "Total");

  endcomp;

  compute after;

      CountryGroup = "Grand Total" ;

  endcomp;

 

  break after CountryGroup/summarize;

  rbreak after / summarize;

run;

ods _all_ close;

Please advice

Thanks


Sample.png
SAS Super FREQ
Posts: 8,865

Re: Proc Report - how to highlight Columns & add a text to the total row

Hi:

  You are asking for the MEAN statistic for your variable TARGET. That is the only statistic that PROC REPORT can give you at the break point on the report. If you want some other value, you will either have to

1) calculate it yourself and change the value within PROC REPORT

2) calculate it yourself outside of PROC REPORT and use a different variable for the summary inside of PROC REPORT

3) change the data before PROC REPORT, probably using FIRST. and/or LAST. logic to keep the first value for a group and then change to the SUM statistic. For example, if you changed your input data, temporarily, like this:

NorthAmerica,Canada,100,1,10

NorthAmerica,Canada,0,2,120

NorthAmerica,Canada,0,3,75

NorthAmerica,USA,115,1,110

NorthAmerica,USA,0,2,20

NorthAmerica,USA,0,3,25

NorthAmerica,USA,0,4,15

NorthAmerica,USA,0,5,5

NorthAmerica,USA,0,6,5

NorthAmerica,USA,0,7,1

NorthAmerica,USA,0,8,7

NorthAmerica,USA,0,9,25

NorthAmerica,USA,0,10,65

Then the SUM statistic would work as you want when you do GROUPING and on the summary line for TARGET. If you remember, back in the first examples of this problem, you had two solutions -- one that used the MEAN (because your TARGET value was on every line) and one that used changed input data numbers and the SUM statistic. This is why you had those two different solutions -- due to the structure of your INPUT data and the way you represented TARGET in the input data. If you are getting a summary report (one row for every countryGroup/Country combination, then you do not need the TARGET number on every row. I don't quite understand the logic of why the values for NorthAmerica/USA changed from 100 to 10 to 5, while the values for CANADA stayed constant at 100 on each row. So there's probably more to understand about your data, but the bottom line is that PROC REPORT can only give you the statistic in the DEFINE statement automatically at a break. You can put another value in the cell using a COMPUTE block, but you have to know what the correct value is.

cynthia

Ask a Question
Discussion stats
  • 1 reply
  • 208 views
  • 0 likes
  • 2 in conversation