SAS Institute. The Power to Know

FOCUS AREAS
Preproduction software & documentation

Base SAS

The WEBMDDB Procedure

The preproduction WEBMDDB procedure reads a SAS data set, constructs a multidimensional database (MDDB) to summarize the input data, and then uses the MDDB to write out a data file. A JavaServer page (JSP) interprets the data file and displays a tabular report in a Web browser. You can design the Web report so that users can expand and drill down into report elements.

In order to produce a WEBMDDB report, you perform the following two tasks:

  1. You use PROC WEBMDDB to create the data file that encapsulates the data.
  2. You deploy a JSP that contains the logic that interprets the data file and displays the WEBMDDB report.

For information about how to perform these tasks, see the following topics:

Note: The software functions only if your site licenses SAS/MDDB® Server Software.

See also:

Downloading and Installing the Software

The WEBMDDB procedure software is contained in a Web archive (WAR) file named webmddb.war that you download from the SAS Web site. (The download page is available from the SAS/MDDB Server Software selection on the Demos and Downloads page.)

After you download webmddb.war, complete the following tasks:

  1. Unpack it into the same location as your JSP application server software. To unpack the file, do one of the following:
    • Enter jar tvf webmddb.war on the command line
    • Use WinZip (Windows systems) or UnZip (UNIX systems).
  2. Copy the executable PROC WEBMDDB image from the WEB-INF/sasexe folder into the appropriate SAS installation location:
    • For Windows systems, copy saswebmd.dll from the windows folder into the SASROOT\mddbserv\sasexe folder
    • For UNIX systems, copy saswebmd from the <unix-platform> folder into the SASROOT/sasexe folder

Note: For additional information about deploying a WAR file, see SAS AppDev Studio Developer's Site.

PROC WEBMDDB Syntax

The procedure syntax consists of the following elements:

The PROC WEBMDDB Statement

Here are the required arguments for the PROC WEBMDDB statement:

DATA=libname.memname
specifies the SAS data set that contains the data for the report.
Tip: Remember to include a LIBNAME statement to assign the libref.
OUT=fileref
specifies the fileref for the data file that is created from the procedure. You define the fileref in a FILENAME statement before you execute the WEBMDDB procedure.

Note: For information about the FILENAME statement, see "Statements" in SAS Language Reference: Dictionary.

JSP='filename.jsp'
specifies a quoted name for the JSP file that will contain the code that enables users to expand and drill down into the report. See Deploying the WEBMDDB Data File for information about the code that you need to enter into this file.

Note: See File Naming Guidelines for recommendations about how to name the SAS, JSP, and WEBMDDB files.

The PROC WEBMDDB statement has one option:

CAPTION='report-title'
specifies a 32-character maximum quoted string that is used as the title for the primary report. You can also set the CAPTION= option to '_none_'.
Default: WEBMDDB Report

The following example includes LIBNAME, FILENAME, and PROC WEBMDDB statements:


  libname sashelp 'c:\program files\sas\v8';
  filename class 'class.webmddb';
  proc webmddb data=sashelp.class out=class jsp='class.jsp' caption='Student Population Report';

The MEASURE Statement

The MEASURE statement defines the columns that are summarized in the report. For each MEASURE statement, you specify one variable and one statistic using the form variable_statistic. For example, to derive the sum of a variable named amount, you specify amount_sum. The available statistics are N, SUM, AVG, NMISS, SUMWGT, MIN, MAX, USS, UWSUM, RANGE, CSS, VAR, STD, STDERR, CV, T, PRT, LCLM, UCLM, PCTSUM, and PCTN.

Note: For more information about the statistics, see "SAS Elementary Statistics Procedures" in Base SAS Procedures Guide.

The MEASURE statement has the following options:

FORMAT=
specifies a SAS or user-defined format in the form format<w>.<d> that is applied to the measure value. For example, in FORMAT=DOLLAR10.2, the w value of 10 specifies a maximum of 10 columns for the value. The d value of 2 specifies that two of these columns are for the decimal part of the value, which leaves eight columns for all the remaining characters in the value. This includes the decimal point, the remaining numeric value, a minus sign if the value is negative, the dollar sign, and commas, if any.

Note: For more information about SAS formats, see "Formats" in SAS Language Reference: Concepts.

PRECISION=numeric-value
specifies the total width of the value that is displayed, including commas, decimal points, and signs.
SCALE=numeric-value
specifies the number of decimal places that are displayed.
WEIGHT=variable-name
for weighted statistics, this option specifies the name of the variable that is used in the calculations.
Requirement: This option is required for weighted statistics.
CAPTION='column-header'
specifies a 32-character maximum quoted string that is displayed in the table as the column header for the measure.
Default: The variable name plus the statistic name. For example, if the variable is named amount and the statistic is SUM, then the default column header is AMOUNT SUM.

If you do not specify a format (using either FORMAT=, PRECISION=, or SCALE=), PROC WEBMDDB applies a format to the measure according to the following rules:

Insert a forward slash / before listing the options. The following MEASURE statement example uses FORMAT= and CAPTION= options.

      measure weight_avg / format=COMMA8.4 caption='Avg Weight';

The DIMENSION Statement

The DIMENSION statement lists the classification variables that are displayed in the report. This statement also sets the drill-down hierarchy for the table in the primary report; for this reason, you must order the classification variables to follow a logical drill-down path into the MDDB. The first classification variable listed is the top level in the table and the subsequent classification variables are the lower levels.

The JSP file includes code that enables users to click each classification variable in the table to display the values of the next variable. When the user reaches the lowest classification variable in the dimension, the detail report is displayed in a new browser window. (See The DETAIL Statement for information about how to specify what information is included in the detail report.)

Note: You can have only one DIMENSION statement per WEBMDDB report.

By default, all classification variables are sorted in ascending order. To specify a different order, enter one of the following keywords after you specify the classification variable name:

In the following example, the classification variable age is sorted in descending order, and the classification variable sex uses the default ascending order.

     dimension age descending sex / caption='Age .. Sex';

The DIMENSION statement has one option:

/ CAPTION='column-header'
specifies a 32-character maximum quoted string that is displayed in the table as the column header for the dimension column.
Default: Column header is left blank.

The DETAIL Statement

The DETAIL statement lists the variables in the input data set that are displayed in a detail report after you click on the lowest classification variable in the dimension. The detail report, which opens in a separate browser window, displays data for the items that make up one row of the lowest level in the dimension. Variable labels from the input data set are used as column headers.

For example, you could create a primary report that shows sales prospects by region, category, and product. A manager could expand the report down to the product classification variable in the school supplies category of the western region. He could then select the product link in the table to display a list of customers who are interested in notebooks and organizers in that area.

The DETAIL statement has one option:

/ CAPTION='report-title'
specifies a 32-character maximum quoted string that is displayed as the title for the detail table. You can also set the CAPTION= option to '_none_'.
Default: WEBMDDB Detail Report
The following code is an example of a DETAIL statement: 
    detail age sex name height weight / caption='Population Detail Report';

PROC WEBMDDB Sample Code

The following PROC WEBMDDB example code builds a report from a data set named SASHELP.CLASS. 

     libname sashelp 'c:\program files\sas\v8';
     filename class 'class.webmddb';

     proc webmddb data=sashelp.class out=class jsp='class.jsp' caption='Student Population Report';

       measure height_min / caption='Min Height';
       measure height_avg / caption='Avg Height';
       measure height_max / caption='Max Height';

       measure weight_min / caption='Min Weight';
       measure weight_avg / caption='Avg Weight';
       measure weight_max / caption='Max Weight';

       dimension age sex  / caption='Age .. Sex';

       detail age sex name height weight / caption='Population Detail Report';
  run;

Deploying the WEBMDDB Data File

Each WEBMDDB report requires an associated JSP file. The JSP file contains the code that enables users to expand and drill down into the report. You name the file with the JSP= argument on the PROC WEBMDDB statement.

Cut and paste the following sample code into your JSP file. To customize the file for your report, change the following items:

This sample assumes that the class.webmddb data file is stored in d:\inetpub\wwwroot\webmddb\. To accommodate Java programming conventions, double backslashes are used in the directory path. 

<jsp:useBean id="classExView"
   class="com.sas.apps.expandablereport.ExpandableReportViewer"
   scope="session">

<jsp:setProperty name="classExView" property="fileName"
    value="d:\\inetpub\\wwwroot\\WEBMDDB\\class.webmddb" />

<jsp:setProperty name="classExView" property="styleClass"
    value="wTable" />

</jsp:useBean>

<html>
<head>
<LINK REL=STYLESHEET HREF="webmddb.css" TYPE="text/css">
</head>

<body>

<H5>EXAMPLE</H5>
<BR><BR>

<%
   classExView.processCommand(request);
   classExView.write(out);
%>

<BR><BR><BR>

</body>
</html>

Also, add any other HTML content that you want to appear in the report.

Note: Refer to your application server software documentation for information about how to deploy JSPs for testing and for production.

File Naming Guidelines

Give the same name to the three files that are used to create the WEBMDDB report. The three files are:

Within the JSP file, you also rename the session object for the WEBMDDB report so that it corresponds with your filenames (see Deploying the WEBMDDB Data File).

For example, if you name your SAS program foo.sas, then name your WEBMDDB file foo.webmddb, and name your JSP file foo.jsp. Within the JSP file, change all session object references to fooExView.

Customizing the Appearance of a WEBMDDB Report

The appearance of the WEBMDDB report is controlled by a cascading style sheet (CSS). To change the default style settings, you edit the CSS (using a text editor, for example). The CSS includes the following style classes:

wTable
controls the appearance of the primary and detail tables. You can specify border width, border color, background images, background colors, and the spacing of the data in the table cells.
wColLab
controls the appearance of the column labels for the primary report table. All column labels use this element. You can set background colors, font colors, and sizes.
wRowLab
controls the appearance of the data row labels. The label is the first item in each row.
wDrillColLab
controls the appearance of the column headings for the detail table.
wDrillRowLab
controls the appearance of the data row labels in the detail table. The label is the first item in each row.
wTitle
controls the appearance of the title of the primary report.
wSubTitle
controls the appearance of the title of the detail table.
wTotLab
controls the appearance of the label for the total row in the primary report. The label is the first item in the total row.
wTotData
controls the appearance of the data displayed in the total row, which is the last row in the primary table.

Receiving Support

The WEBMDDB procedure is not supported by SAS Institute Technical Support. Support is via email only, at Base.Research@sas.com.

Additional Information Resources

Note: SAS OnlineDoc is available in HTML and PDF format from the SAS Web site.

Glossary

application server
a server that is used for storing applications. Users can access and use these server applications instead of loading the applications on their client machines. The application that the client runs is stored on the client. Requests are sent to the server for processing, and the results are returned to the client. In this way, little information is processed by the client, and nearly everything is done by the server.
cascading style sheet
a set of specifications that control how HTML elements are displayed in a Web browser. For example, you can use a CSS to specify fonts, sizes, and colors of the text in HTML documents. Cascading style sheets make it easier for authors of HTML documents to maintain a consistent style on multiple pages.
classification variable
a variable whose values classify the observations in a data set into different groups that are meaningful for analysis.
fileref
a short name (or alias) for the full physical name of an external file. A SAS FILENAME statement maps the fileref to the full physical name.
JAR file
a Java Archive file. The JAR file format is used for aggregating many files into one file. JAR files have the file extension .jar.
JavaServer page (JSP)
a type of servlet that enables users to create Java classes through HTML.
JavaServer Pages technology
an extensible Web technology that uses template data, custom elements, scripting languages, and server-side Java objects to return dynamic content to a client. Typically the template data is either HTML elements or XML elements, and in many cases the client is a Web browser.
libref
a short name (or alias) for the full physical name of a SAS library. A SAS LIBNAME statement maps the libref to the full physical name. A libref is the first part of a multi-level SAS filename and indicates the SAS data library in which a SAS file is stored. For example, in the name SASUSER.ACCTS, SASUSER is the libref, and ACCTS is a file in the library that the SASUSER libref refers to.
MDDB (multidimensional database)
a specialized data storage structure in which data is presummarized and cross-tabulated and then stored as individual cells in a matrix format, rather than in the row-and-column format of relational database tables. The source data can come either from a data warehouse or from other data sources. MDDBs can give users quick, unlimited views of multiple relationships in large quantities of summarized data.
SAS data set
data values that are organized as a table of observations (rows) and variables (columns) that can be processed by SAS software. A SAS data set also contains descriptor information such as the data types and lengths of the variables, as well as which engine was used to create the data.
servlet
a Java program that runs on a Web server. Servlets can be considered a complementary technology to applets, which run in Web browsers. Unlike applet code, servlet code does not have to be downloaded to a Web browser. Instead, servlets send HTML or other appropriate content back to a browser or to another type of Web-based client application.
WAR file
a Java archive (JAR) file that is packaged to deploy as a Web application. The WAR file contains the complete directory structure and all files that define the application. WAR files have the file extension .war.

Your Turn

The developers, testers and documentation folk that bring you SAS software are very excited about the potential of these new capabilities. Please send electronic mail to Base.Research@sas.com with your comments.