Service Packs for the SAS9 Platform." />
SUPPORT / SAMPLES & SAS NOTES
 

Support

Sample 26113: Create a sample marketing analysis dashboard

DetailsCodeOutputDownloadsAboutRate It

This sample uses the GCHART and GPLOT procedures in conjunction with the Annotate facility in the SAS/GRAPH software to create a variety of indicators, and then uses the GREPLAY procedure to arrange the indicators into a dashboard.

Note: This dashboard is described on p. 201 of Information Dashboard Design (Few, Stephen. 2006. Sebastopol, CA. O'Reilly Media, Inc.).

For additional information about creating dashboard applications with SAS/GRAPH software, see SAS/GRAPH Dashboard Samples.

This sample creates a full-blown dashboard for marketing analysis. It creates the following custom charts:

  • PLOT1: MTD Compared to Target
  • PLOT2: Percent of Total Visitors Today
  • PLOT3: Visitors - Last 12 months
  • PLOT4: Visitors - This month
  • PLOT5: Visitors - Today
  • PLOT6: Products - Sparklines
  • PLOT7: Products - Revenue & Viewed
  • PLOT8: Top 10 - purchased together, but not displayed together
  • PLOT9: Referral Sites - sparklines
  • PLOT10: Referral Sites - table
  • PLOT11: Top 10 - displayed together, but rarely purchased together

It then uses the GREPLAY procedure with a custom layout template to place the charts onto a telesales dashboard. Here is the layout of the custom template:

Layout of the maketing analysis dashboard

About the Data Sets

This sample creates the following SAS data sets for the plots that are used on the dashboard:

  • DATA1: data for MTD Compared to Target
  • DATA2: data for Percent of Total Visitors Today
  • DATA3: data for Visitors - Last 12 months
  • DATA4: data for Visitors - This month
  • DATA5: data for Visitors - Today
  • DATA6: data for Products - Sparklines
  • DATA7: data for Products - Revenue & Viewed
  • DATA8: data for Top 10 - purchased together, but not displayed together
  • DATA9: data for Referral Sites - sparklines
  • DATA10: data for Referral Sites - table
  • DATA11: data for Top 10 - displayed together, but rarely purchased together

DATA12 and DATA13 provide data for an overlay that contains titles for PLOT6, PLOT7, PLOT9, and PLOT10.

You can see the definition for these data sets near the top of the SAS program for this sample. The numbering for the plots and SAS data sets corresponds to the location of the charts in the dashboard layout.

Here are the variables that you must use in each of the data sets:

Data Set Name Variable Name Description
DATA1 BARNAME specifies the bar name. Can be 'Visitors' or 'Orders'.
VALUE specifies the MTD value (bar length).
TARGET specifies the target for the MTD.
DATA2 BARNAME specifies the bar name. Can be 'Registered' or 'Repeat'.
VALUE specifies the percent of the total visitors for today (bar length).
TARGET specifies the target value between the low and mid range.
TARGET2 specifies the target value between the mid and high range.
DATA3 YEAR specifies the year as YYYY. For example, 2008.
MONTH specifies the month.
VISITOR_DEVIATION specifies the percent centered around zero.
DATA4 DAYS specifies the number of days so far for this month.
VISITOR_DEVIATION specifies the percent centered around zero.
DATA5 HOURS specifies the hours so far for today.
VISITOR_DEVIATION specifies the percent centered around zero.
DATA6 PRODUCT specifies the product name. The name must match the product names in DATA7.
TIMESTAMP specifies a numeric time stamp on the X axis of the sparkline.
PERCENT_OF_TARGET specifies the percent of the sales target.
DATA7 PRODUCT specifies the product name. The name must match the product names in DATA6.
REVENUE specifies the percent of the revenue.
VIEWED specifies the percent of the viewed products.
DATA8 PRODUCT1 specifies the name of a product.
PRODUCT2 specifies the name of a product.
VALUE specifies the percent of sales.
DATA9 PRODUCT specifies the name of the referring Web site. The names must match the names in DATA10.
TIMESTAMP specifies a numeric time stamp on the X axis of the sparkline.
PERCENT_OF_TARGET specifies the percent of the target.
DATA10 PRODUCT specifies the name of the referring Web site. The names must match the names in DATA9.
REFERRAL_COUNT specifies the referral count.
REFERRAL_PCT specifies the percent of referrals.
SINCE_YEAR_AGO specifies the percent of referrals since a year ago.
AVERAGE_REVENUE specifies the average revenue.
DATA11 PRODUCT1

specifies the name of a product.

PRODUCT2 specifies the name of a product.
VALUE specifies the percent of sales.

You can change the data in the SAS program and rerun it to see how the changes affect the dashboard. You can also replace the hard-coded data with a query that pulls similar data from your live databases to plot on the dashboard. However, the data from your databases must be similar and you must use the same variable names.

How the Plots are Created

Here are some notes on how this sample generates the plots for the dashboard:

PLOT1

To generate PLOT1, the GCHART procedure is used to draw a horizontal bar, and gray shaded areas are added behind the bars to show whether the bars are in an acceptable range compared to the target value. The Annotate BAR function is used to create the gray areas. The Annotate WHEN='b' option is used to draw the gray areas before the real bars are drawn. The GCHART HBAR REF=1.00 option is used to draw a reference line at 100% along the axis.

The GCHART HBAR HTML= option is used to add chart tips and drill-down links. Here is the variable that stores the chart tip and drill-down information:

  html=
   'title='||quote( trim(left(barname))||': '||trim(left(put(value,percentn6.0))) )
    ||' '||
   'href="#plot1_drill"';

Note that the #plot1_drill is actually an HTML anchor that is created on the same output page as the dashboard.

PLOT2

To create PLOT2, the process that was used to create PLOT1 is followed, except that three annotated shades of gray are placed behind the bars. The Annotate BAR function is used to create the three gray areas.

PLOT3

To create PLOT3, the GPLOT procedure is used to draw a basic line plot. The Annotate BAR function is then used to draw a gray shaded area below the 0% line. The bottom-left corner of the gray area starts at the bottom-left corner of the area inside of the axes. The top-right corner of the gray area is at the right-edge of the axis at the zero line.

For the horizontal axis, to replicate Stephen Few's tightly packed and staggered year and month abbreviations, some customizations are required. The order of the horizontal axis is controlled by a string, which is the month and year combined, such as 2004_07. To get the desired tick marks on the axis, the default strings are suppressed, and the Annotate facility is used to create the staggered year and month abbreviations using the user-defined two-character month values and the year below the lowest month value of each year.

Both the plot markers and the annotated two-character month annotations have chart tips and HTML drill-down links to the detailed report.

PLOT4 and PLOT5

To create PLOT4 and PLOT5, the process that was used to create PLOT3 is used, except that the process for creating the horizontal axis is simpler.

PLOT6

To create the sparklines in PLOT6, the GPLOT procedure is used to overlay three lines on the sample plot. An offset is added successively to the Y axis of each line to make each line appear on top of the other. For each sparkline, a reference line is added on the vertical axis at the 100% target to show how the actual value compares to the target over time. AXIS statements are used to suppress the normal GPLOT axis.

Since the sparklines do not have markers, HTML chart tips and drill-down links cannot be added to the sparklines. To add chart tip and drill-down functionality, an invisible annotated bar is added around the entire graph area. The viewer can click on the bar to drill down to the detailed data.

It is imperative that the exact same Y variable values (products) be used to draw the sparklines as those that were used to draw the bar chart (PLOT7). Also, the sparklines must be in the same order as the bars in PLOT7.

PLOT7

To create PLOT7, the GCHART procedure is used to create a simple horizontal bar chart (HBAR). The GCHART HTML= option is used to identify the variable that contains the HTML chart tip and drill-down information for the individual bars. The Annotate MOVE and DRAW functions are then used to draw a heavy black annotated mark on the bar chart to show the percent viewed.

The legend above the bar chart is a custom legend that is created in PLOT12 and overlaid on this chart using GREPLAY when the dashboard is created.

PLOT8

To create PLOT8, annotated text labels are used to create the table in a GRSEG that can be replayed with the GREPLAY procedure. To add chart tip and drill-down functionality, for simplicity, instead of using the Annotate HTML= option with each text label, an invisible Annotate box is created around the entire table to create a single chart tip and drill-down link.

PLOT9

To create PLOT9, the same process that was used to create PLOT6 is used. In this case, the product is actually the name of the referring Web site. The product variable was retained to allow the code to be reused as-is, even though the value that is stored in the variable is not a product name.

PLOT10

To create PLOT10, the GCHART procedure is used to create a horizontal bar chart (HBAR), but to make the bars invisible, the color of the bars is set to the background color as follows:

pattern1 v=s c=&backcolor;

Columns of text are then annotated in the space where the bars are normally seen. A drill-down link to the Web site is then added to the midpoint of each bar. Since the GCHART procedure does not support adding chart tip and drill-down links to the bar midpoints, an invisible Annotate bar is created on each of the bars. The drill-down link to the Web site is then added to the invisible bars. The Web sites used in this sample are the sample Web sites that are used in Stephen Few's book. You can replace these links with links to your own Web sites.

To add chart tip and drill-down functionality to the chart, an invisible Annotate bar is drawn around the entire chart. The viewer can click on the bar to drill down to the detailed report.

PLOT11

To create PLOT11, the same process that was used to create PLOT8 is used.

PLOT12

PLOT12 is an overlay that provides the titles for PLOT6 and PLOT7. To create PLOT12, the Annotate facility is used to write the large title, the column headers for the sections of the sparklines and the bar chart, and the custom legend. The Annotate MOVE and DRAW functions are then used to draw the line just below the titles and text.

PLOT13

PLOT13 is an overlay that provides titles for PLOT9 and PLOT10. Annotate is used to write the large title and the column headers for the sections of the sparklines and the tabular columns of data.

How the Dashboard is Created

When the plots are generated, the output is stored in GRSEGs. The GREPLAY procedure is then used to assemble the plots onto a single page using a custom GREPLAY template. The custom GREPLAY template is defined by specifying four X and Y coordinates for the rectangular area that will hold each graph. The units of the X and Y coordinates are the percentage of the screen in the X and Y directions, with 0,0 being at the bottom-left corner. Each rectangular area is assigned an ID number. Here are the coordinates that place the Products sparkline in the middle-left part of the dashboard in area 6:

   6/llx =  0  lly =30
     ulx =  0  uly =60
     urx = 15  ury =60
     lrx = 15  lry =30

where LLX is the lower-left X coordinate, LLY is the lower-left Y coordinate, and so on.

Since the GREPLAY procedure allows you to overlap the rectangular areas, area 0 is defined as covering the entire dashboard. The GSLIDE procedure and the Annotate facility are used to place the title text at the top-center part of the dashboard. Here are the coordinates:

   0/llx = 0   lly =  0
     ulx = 0   uly =100
     urx =100  ury =100
     lrx =100  lry =  0

Area 12 holds a title that overlaps areas 6 and 7. Here are the coordinates:

  12/llx =  0  lly =30
     ulx =  0  uly =60
     urx = 55  ury =60
     lrx = 55  lry =30

Here is the GREPLAY procedure TREPLAY action statement (template replay) that draws the appropriate charts into the desired areas of the custom GREPLAY template:

 treplay
  0:titles
  1:plot1            2:plot2
  3:plot3  4:plot4   5:plot5
  6:plot6  7:plot7   8:plot8
      12:plot12
  9:plot9 10:plot10 11:plot11
      13:plot13

Note: For convenience and code readability, spacing is used in the TREPLAY action statement to make the layout correspond to the actual location of the charts in relation to one another.

Notice that the PLOT7 GRSEG is played into area 7.

For each chart on the dashboard, this sample adds a drill-down link to a detailed table of the data for the chart. To keep this sample self-contained, the drill-down tables are placed on the same output page just below the dashboard, and each table is identified using an HTML anchor as follows:

ods html anchor="&anchor_name";

However, in your case, these drill-down links would likely be to separate detail tables or charts on a separate page.

In this sample, the anchor names are created when the bar charts are created as follows:

   htmlvar=
   'title='||
    quote(
    'Quarter: '|| trim(left(quarter)) ||'0D'x||
    'Target: '|| trim(left(put(target,&value_format))) ||'0D'x||
    'Actual: '|| trim(left(put(actual,&value_format))) ||'0D'x||
    'Actual as Percent of Target: '|| trim(left(put(percent_of_target,percent6.0)))
     ) || ' '||
   'href="#'||trim(left("&grseg_name"))||'"';

To keep the naming simple and consistent, the GRSEG names are used as the anchor names. While you are viewing the dashboard, if you move your mouse pointer over each bar, the data value for the bar is displayed in the HTML chart tip. If you click a bar, your browser scrolls down to the HTML anchor to show you the table of the data for the bar.




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.