topic Proc Report - how to highlight Columns & add a text to the total row in SAS Procedures

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

tparvaiz — Fri, 22 Feb 2013 00:21:35 GMT

Hi,

I am trying to put a report together showing Country Group, Country, target(on the left hand side) and date (going across @ the top) and volume in the middle.. at the end of every CountryGroup I am able to show the total of volume: something like this

Country Group Country, target Day1 Day2

NorthAmerica Canada 200 310 120

USA 250 10 320

320 440

I want to modify the above sample report as follows

1) in the ROW, where I am showing sum of the volumes, I want to say total of the Country Group.

2) I want to add the total of the target as well

3) want to highlight volumes that are greater than the target

so the above report should look something like this

Country Group Country, target Day1 Day2

NorthAmerica Canada 200 310 120

USA 250 10 320

North America Total 450 320 440

Here is the code that I've used

proc report data=reg headline

style(column)={font_face=times font_size=1.4}

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

column CountryGroup Country allocated dayNo, Volume;

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 allocated / group 'Allocated ' ORDER=INTERNAL style(column)=[background=gray foreground=white font_weight=bold];

define dayNo / across 'Day ' ORDER=INTERNAL;

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

break after CountryGroup / ul skip summarize suppress;

rbreak after / dol skip summarize;

Thanks in advance

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

Cynthia_sas — Fri, 22 Feb 2013 06:36:25 GMT

Hi:

I have a hard time understanding how your code generated the output you show. For example, Where is the TARGET item? You don't show TARGET in the COLUMN statement? You show a report item called "Allocated", but that is not shown on your output. You have COUNTRYGROUP, COUNTRY and ALLOCATED as GROUP items. Yet, you do not show ALLOCATED.

It was useful to see the code you're using. But in order for anyone to help you, they have to make fake data. You are the only person who knows what the structure of your data looks like. The fake data that someone makes might not be in the same structure as your real data. That would mean the solution they devise for their fake data might NOT be the right solution for the structure of your real data.

If you cannot post your real data, then a very helpful thing to do is for you to generate some test data (possibly in a DATALINES program) or to recast your program using one of the SASHELP datasets. Before you worry about traffic lighting to highlight the values that are greater than the target item, you need to have target on the report.

The way you will label the Total at the break between CountryGroup values will be with a COMPUTE block. The way that you will do traffic lighting or highlighting will be with a COMPUTE block and a CALL DEFINE statement. Examples of both techniques have been posted in this forum before. Also, since you have ACROSS variables on your report, you will need to learn how to use the absolute column number for your CALL DEFINE statement, inside your COMPUTE block and there have been examples of that posted, too.

I hope I have given you enough pointers to get you started.

cynthia

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

BrunoMueller — Fri, 22 Feb 2013 13:09:34 GMT

I used the tips from Cynthia and created some sample data and a Proc Report, it should give you a good start:

data someData;
  infile cards dsd dlm="," missover; 
  input
    countryGroup : $32.
    Country : $32.
    target : 8.
    Day : 8.
    dayValue : 8.
  ;
  cards; 
NorthAmerica,Canada,200,1,310
NorthAmerica,Canada,200,2,120
NorthAmerica,USA,250,1,10
NorthAmerica,USA,250,2,320
; 

proc report data=someData nowindows; 
  column countryGroup Country target day, dayValue _dummy;
  define countryGroup / group; 
  define country / group; 
  define target / analysis mean; 
  define day / across; 
  define dayValue / analysis sum; 
  define _dummy / computed; 

  compute _dummy /;

    * depends on the number of days;
    array myDays{*} _c4_ _c5_;

    * we are in a plain data line;
    if missing(_break_) then do; 

      do i = 1 to dim(myDays);
        if myDays{i} > target.mean then do; 
          call define(vname(myDays{i}), "style", "style={background=cx997086}");
        end; 
      end; 

      targetTotal + target.mean;
    end; 

    * we are in a summary line;
    if upcase(_break_) = "COUNTRYGROUP" then do; 
      countryGroup = catx(" ", countryGroup, "Total");
      target.mean = targetTotal;
    end; 
  endcomp; 

  break after countryGroup / summarize; 
run;

Bruno

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

tparvaiz — Sat, 23 Feb 2013 00:52:12 GMT

cynthia, Thanks for your assistance.

Bruno, thanks for your code

can you please assist me with the following

1) can you please explain what's the c4 and c5 doing in the following:

array myDays{*} _c4_ _c5_

... using your code, highlighting is applied for the first 2 days only, I have 10 days in my data, how to modify it for 10 days?

2) how to add Grand total of all the countryGroup @ the end?

3) _dummy column is showing up in the report, how can I get rid of it?

rest seems to be perfect

Thanks again for your assistance

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

Cynthia_sas — Sat, 23 Feb 2013 03:08:30 GMT

Hi:

I'll chime in here, since Bruno used my hints from the earlier post.

1) _c4_ and _c5_ are absolute column numbers. When you use an ACROSS variable you could have multiple unique values for the ACROSS. Each unique value gets a column going from left to right. How can ODS tell which is column for the volume on Day1 versus the column for Day 2??? During a "pre-processing" phase, PROC REPORT gives absolute numbers to the items that are ACROSS items. So Bruno has

column countryGroup Country target day, dayValue _dummy;

this column statement. There are 3 "non-ACROSS" report items before the DAY variable, So the first column ACROSS (Day1) will be _c4_ and the second column ACROSS ( Day2 ) will be _c5_ . Day 3 will be _c6_ and Day 4 will be _c7_, etc, etc. You would have to add the absolute column numbers for the other days as aneeded (or if you know SAS macro code, you can calculate the correct number of days with an SQL query and create a list of var names in macro variables). See the code example below (with more days and 2 country groups). This paper shows one macro approach to figure out the number of automatic column variables that you will have/need:

http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf starting on page 12.

2) To add a "grand total" at the bottom (or top) of the report, you would add an RBREAK statement:

RBREAK AFTER/SUMMARIZE;

3) There is a way to write the program without using _dummy. Bruno was using a feature of PROC REPORT that allows you to change or alter any items to the left of an item on the COLUMN statement. However, if you wanted to put an empty line between the groups or you wanted to write a line of text AFTER the group, you would need a COMPUTE block such as I show in my program. If you are going to use the _dummy approach, then you will want to look up and use the NOPRINT option for the DEFINE statement.

cynthia

data someData;
infile cards dsd dlm="," missover;
input
    countryGroup : $32.
    Country : $32.
    target : 8.
    Day : 8.
    volume : 8.
;
cards;
NorthAmerica,Canada,150,1,310
NorthAmerica,Canada,75,2,120
NorthAmerica,Canada,75,3,275
NorthAmerica,USA,100,1,10
NorthAmerica,USA,100,2,320
NorthAmerica,USA,50,3,275
SouthAmerica,Brazil,75,1,150
SouthAmerica,Brazil,75,2,150
SouthAmerica,Paraguay,100,1,100
SouthAmerica,Paraguay,50,2,100
SouthAmerica,Peru,50,1,178
SouthAmerica,Peru,50,2,100
SouthAmerica,Peru,50,3,100
;
run;

options missing=0;
ods html file='c:\temp\absolute_cols.html' style=htmlblue;
proc report data=someData nowindows
style(summary)=Header;
title 'Alternate Solution';
column countryGroup Country target day, volume;
define countryGroup / group;
define country / group;
define target / analysis sum;
define day / across;
define volume / analysis sum;

compute volume /;

    * depends on the number of days;
    * every unique DAY value will get a unique absolute _c?_ number;
    * day1 vol=_c4_, day2 vol=_c5_, day3 vol=_c6_;
    * day4 vol=_c7_, day5 vol=_c8_, day6 vol =_c9_;
    * day7 vol=_c10_, day8 vol=_c11_, day9 vol=_c12_, day10 vol=_c13_;
    * if more than 3 days, need to alter array or calc correct array;
    * number of members using macro variables;
    array myDays{*} _c4_ _c5_ _c6_;

    * at a plain (non-break) report row;
    if missing(_break_) then do;

      do i = 1 to dim(myDays);
        if myDays{i} > target.sum then do;
          call define(vname(myDays{i}), "style", "style={background=cx997086}");
        end;
      end;

    end;
   endcomp;

   compute after countrygroup;
    * at a summary row;
      countryGroup = catx(" ", countryGroup, "Total");
      line ' ';
endcomp;
compute after;
    * at a grand total Row;
      countryGroup = "Grand Total" ;
endcomp;
break after countrygroup/summarize;
rbreak after / summarize;
run;
ods html close;

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

tparvaiz — Sat, 23 Feb 2013 08:51:54 GMT

Hi, here is the code that I've used... it's working fine with the exception that now the volumes that are greater than the target are not highlighting (only some are highlighting where the target is 0). may I know how to fix it.

proc report data= someData headline

style(column)={font_face=times font_size=1.4}

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

column CountryGroup Country target day, volume;

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 sum;

define day / across 'Day ' ORDER=INTERNAL;

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

compute volume /;

array myDays{*} _c4_ _c5_ _c6_ _c7_ _c8_ _c9_ c10_ _c11_ c12_ _c13_ _c14_;

if missing(_break_) then do;

do i = 1 to dim(myDays);

if myDays{i} > target.sum then do;

call define(vname(myDays{i}), "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;

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

Cynthia_sas — Sat, 23 Feb 2013 17:14:08 GMT

Hi:

I do not know how to fix your issue. At this point, it seems to me that there might be something different between your data and the data that Bruno and I used. Your code looks almost exactly like what was previously posted. That just leaves your data. When Bruno and I used very similar code, we did get highlighting. Are you getting error messages in the SAS Log? Is there something different about the structure of your data than the stucture that Bruno and I used? What destination are you using to create your output? I find your use of HEADLINE to be confusing. That is a LISTING-destination (output window) only option. It will not work in other ODS destinations. Neither Bruno nor I had HEADLINE in our code, so the only reason I can think of for putting HEADLINE back into your code is that you are creating LISTING output, in which case, none of the STYLE overrides will work. The CALL DEFINE will not work with the LISTING destination.

You have two examples that both work using data in a particular structure. If you use the same code and it doesn't work with your data, there are not many possibilities: either there is something different between how your data are structured and how the test data was structured; or, you have the wrong number of days in the array statement? _c4_ - _c14_ is 11 days...I thought you had 10 days? 10 days as ACROSS items would be _c4_ - _c13_ ?? Are you getting array subscript out of range messages? Or, another possibility is that there is some kind of bug with PROC REPORT; or another possibility is that you are trying to use this code perhaps in a stored process with a client application that might not fully support PROC REPORT.

There was one difference between my program and Bruno's program that may make a difference. Bruno's fake data used the MEAN statistic for TARGET, because he had the entire target value on every data row (so he needed to take the mean so you would get 300 on a row instead of 600, for example). My fake data used the SUM statistic for TARGET, because my fake data had smaller target values on every data row, which added up to the full target number you showed originally.

At this point, I can only think of 2 or 3 possibilities. Either something is odd with your data or there's something strange going on with PROC REPORT. To eliminate PROC REPORT as a possibility, I'd recommend seeing whether "hard-coding" the IF statement works with your data structure (instead of using an ARRAY statement) as shown below. With my fake data, having 10 days of values, the below code produced the screen shot, which clearly shows the highlighting when the absolute column value is GT the target for the row.

If you run my code using my fake data and it works for you. Then you need to try the SAME code with your data and if it doesn't work, then that indicates that there is something different about the structure of your data. And, as I told you in my first post

"You are the only person who knows what the structure of your data looks like. The fake data that someone makes might not be in the same structure as your real data. That would mean the solution they devise for their fake data might NOT be the right solution for the structure of your real data."

So if there is a data problem, no one here can help you with that. Since you never posted an example of your data, it is nearly impossible for anyone to guess the exact structure of your real data? Is your data pre-summarized? How does the target value get populated?

At this point, your best resource is to work with Tech Support. They can look at ALL your code, including ODS statements and ALL your data and keep your data confidential. They could help you figure out whether the issue is an issue with PROC REPORT (which I doubt, but is a remote possibility) or whether the issue is an issue with your data.

In the code below, I removed the ARRAY statement and did the "old school" method of referencing the absolute column names. I also removed the HEADLINE option and switched day,volume in the COLUMN statement to volume,day (so that you would not have empty cells underneath the days). As you can see from the screenshot, with 10 days for USA, the highlighting worked correctly. I got the same results, as shown in the screenshot when I used either method #1 or method #2 (the ARRAY method). I am not reposting the Method 2 code again, as it is the same as what has already been posted.

Since you have not posted your real data, I figure it's because you are not allowed to. In this case, it is impossible to offer any more help and Tech Support will be your best bet. To open a track with Tech Support, fill out the form at this link: http://support.sas.com/ctx/supportform/createForm

cynthia

data someData2;
** data has 10 days for USA;
infile cards dsd dlm="," missover;
input
    countryGroup : $32.
    Country : $32.
    target : 8.
    Day : 8.
    volume : 8.
;
cards;
NorthAmerica,Canada,100,1,310
NorthAmerica,Canada,100,2,120
NorthAmerica,Canada,100,3,275
NorthAmerica,USA,100,1,110
NorthAmerica,USA,10,2,320
NorthAmerica,USA,5,3,275
NorthAmerica,USA,5,4,125
NorthAmerica,USA,5,5,275
NorthAmerica,USA,5,6,125
NorthAmerica,USA,5,7,115
NorthAmerica,USA,5,8,475
NorthAmerica,USA,5,9,125
NorthAmerica,USA,5,10,675
SouthAmerica,Brazil,75,1,150
SouthAmerica,Brazil,75,2,150
SouthAmerica,Paraguay,100,1,100
SouthAmerica,Paraguay,50,2,100
SouthAmerica,Peru,50,1,178
SouthAmerica,Peru,50,2,100
SouthAmerica,Peru,50,3,100
;
run;

options missing=0;
ods html file='c:\temp\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 '1) With 10 Days for USA';
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 sum;
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.sum then
          call define('_c4_', "style", "style={background=cx997086}");
        if _c5_ > target.sum then
          call define('_c5_', "style", "style={background=cx997086}");
        if _c6_ > target.sum then
          call define('_c6_', "style", "style={background=cx997086}");
        if _c7_ > target.sum then
          call define('_c7_', "style", "style={background=cx997086}");
        if _c8_ > target.sum then
          call define('_c8_', "style", "style={background=cx997086}");
        if _c9_ > target.sum then
          call define('_c9_', "style", "style={background=cx997086}");
        if _c10_ > target.sum then
          call define('_c10_', "style", "style={background=cx997086}");
        if _c11_ > target.sum then
          call define('_c11_', "style", "style={background=cx997086}");
        if _c12_ > target.sum then
          call define('_c12_', "style", "style={background=cx997086}");
        if _c13_ > target.sum 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;

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

tparvaiz — Sun, 24 Feb 2013 09:18:18 GMT

Hi Cynthia/Bruno,

Thanks for your suggestion... it worked...

I really appreciate all the assistance provided... you guys rock!!!

Best Regards