Overview

By default, SAS Web Report Studio does not enable you to add totals to graphs. In order to see total values in a graph, your data source must include a value that represents the total value. This sample uses the SASHELP.PRDSALE table to demonstrate how you can accumulate a total value for all months, and then produce a bar chart that displays a bar that represents the total.

This sample also includes modified instructions for calculating a total for values that are not dates.

Note: This sample was created using SAS Web Report Studio 4.3, but this task also can be done with version 3.1 or 4.2.

Prerequisites

This sample assumes that you know how to submit SAS code, register tables in metadata using SAS^® Management Console, make user-defined formats available in your BI deployment, and create an information map using SAS^® Information Map Studio.

This sample is intended for intermediate to advanced users.

Sort the data by the variable being totaled

Before you can calculate the total, you must sort the values by the variable that is being totaled. For this sample, the following code sorts SASHELP.PRDSALE by month.

proc sort data=sashelp.prdsale out=sort_prdsale;
by month;
run;

Calculate the month total

The following code uses first. logic in order to calculate a total value for each month. The date used for the total value can be any date that is not a true date value that is also in your data. This sample uses 999999, which, when displayed as a SAS date, is November 27, 1997.

libname new "c:\new";  /* Create a location to store reshaped data */
data new.new_prdsale_with_total;
retain ytd;
  set sort_prdsale end=last;
    by month;
  if first.month then total=0;
  total+Actual;
 if last.month then do; 
 	total=actual;/*output;*/
	ytd+total;output;
  end;
if last then do;
	month=999999;
total=ytd;output;
end;
run;

Register the table

In SAS Management Console, register the new_prdsale_with_total table that you created previously.

Note: For help, see Establishing Connectivity to a Library of SAS Data Sets.

Create a user-defined format

To make user-defined formats available for selection in SAS Business Intelligence clients, you typically place a catalog named formats.sas7bcat in a designated directory on each machine that is hosting a SAS Workspace Server.

For this sample, the following code creates a user-defined format that will be applied to the date. The code writes the format to the catalog in \Lev1\SASMain\SASEnvironment\SASFormats.

libname exists "c:\";
proc format lib=exists;
  value myfmt
    999999='Total'
	other=[monname3.];
run;

/* Change the path below to your specific BI path location */

libname sasenv '<SAS-config-dir>\Lev1\SASMain\SASEnvironment\SASFormats';
proc catalog cat=exists.formats;
   copy out=sasenv.formats;
run;

Note: For more information about making user-defined formats available in a SAS Business Intelligence deployment, see SAS Note 40103.

Create the information map

1. In SAS Information Map Studio, insert the new_prdsale_with_total table into a new map.
2. Open the properties for the Month data item, and click the Classifications, Aggregations, Formats tab.
3. For the Format type, select User-defined, and then select MYFMT. (which you created previously).
4. Save the information map.

Use the total in a bar chart

1. In SAS Web Report Studio, create a new report using the information map that you created previously.
2. In Edit mode, place a bar chart into the layout. Assign Total to Bar Height and Month to Bars.
3. Open the properties for the bar chart, click the Bar tab, and select the Show data values option.
4. Click OK, and then click View to display the chart.

In the following bar chart, the total bar matches the height of all sales.

(Variation) Calculating a total for values that are not dates

If your bar data item is not a date value, you do not need to use the user-defined format explained in the previous sections of this sample. You do still need to sort your sort your data and your code is little different. The modifications are explained next.

Before you can calculate the total, you must sort the values by the variable that is being totaled. For this sample, the following code sorts SASHELP.PRDSALE by Product.

proc sort data=sashelp.prdsale out=sort_prdsale;
by product;
run;

The following code uses first. logic in order to calculate a total value for each Product. The Product used for the total value can be any text value that you want to display. This sample uses "Total" as the Product for the Total value.

libname new "c:\new";  /* Create a location to store reshaped data */
data new.new_prdsale_with_total;
retain ytd;
  set sort_prdsale end=last;
    by product;
  if first.product then total=0;
  total+Actual;
 if last.product then do; 
 	total=actual;/*output;*/
	ytd+total;output;
  end;
if last then do;
	Product="Total";
total=ytd;output;
end;
run;

To create the bar chart, follow the previous instructions in Register the table, Create the information map, and Use the total in a bar chart.

Additional Documentation

For more information about SAS Web Report Studio, see the SAS Web Report Studio documentation page.

For more information about SAS Information Map Studio, see the SAS Information Map Studio documentation page.

For more information about SAS Management Console, see the SAS Management Console documentation page.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Operating System and Release Information