TEMPLATE Procedure: Creating Table Templates

Example 3: Creating a New Table Template

Features:

Table attributes:: DOUBLE_SPACE=; OVERLINE=; UNDERLINE=

DEFINE statement (for columns):: GENERIC= attribute; HEADER= attribute; ID= attribute; STYLE= attribute; VJUST= attribute

DEFINE statement (for headers):: TEXT statement; STYLE= attribute; SPACE= attribute

DEFINE FOOTER statement

HEADER statement

MVAR statement

Other features:

Other ODS features:: FILE statement with ODS= option; PUT statement with _ODS_ argument

Data set:

Charity

Details

This example creates a custom table template for an output data set that PROC MEANS produces.

Note: This example uses filenames that might not be valid in all operating environments. To successfully run the example in your operating environment, you might need to change the file specifications. See ODS HTML Statements for Running Examples in Different Operating Environments.

Program 1: Producing an Output Data Set with PROC MEANS

options nodate pageno=1 pagesize=60 linesize=72;

proc means data=Charity descendTypes charType noprint;
   class School Year;
   var moneyRaised;
   types () School year;
   output out=top3list sum= mean=
      idgroup ( max(moneyRaised) out[3](moneyRaised name school year)= )
      / autoname;
   run;

proc print data=top3list noobs;
   title "Simple PROC PRINT of the Output Data Set";
run;

Program Description

Set the SAS system options. The OPTIONS statement controls several aspects of the LISTING output. None of these options affects the HTML output.

options nodate pageno=1 pagesize=60 linesize=72;

Compute the descriptive statistics, and specify the options and subgroups for analysis. This PROC MEANS step analyzes the data for the one-way combination of the class variables and across all observations. It creates an output data set that includes variables for the total and average amount of money raised. The data set also includes new variables for the top three amounts of money raised, the names of the three students who raised the money, the years when the students raised the money, and the schools that the students attended.

proc means data=Charity descendTypes charType noprint;
   class School Year;
   var moneyRaised;
   types () School year;
   output out=top3list sum= mean=
      idgroup ( max(moneyRaised) out[3](moneyRaised name school year)= )
      / autoname;
   run;

Print the report. This PROC PRINT step generates HTML output of the output data set that PROC MEANS created.

proc print data=top3list noobs;
   title "Simple PROC PRINT of the Output Data Set";
run;

Default PROC PRINT Output

Program 2: Building a Custom Table Template for the TopN Report

options nodate pageno=1 pagesize=60 linesize=72;

proc template;
   define table means.topn;

   mvar first_year last_year sysdate9;

   column class sum mean (raised) (name) (school) (year);

    double_space=on;
    overline=on;
    underline=on;

   header table_header_1 table_header_2;

   define table_header_1;
      text "Top Three Fund Raisers";
      style=header{fontsize=6};
   end;

  define table_header_2;
      text "from " first_year " to " last_year;
      space=1;
  end;

   define footer table_footer;
      text "(report generated on " sysdate9 ")";
      split="*";
      style=header{fontsize=2};
   end;

   define class;
      generic=on;
      id=on;
      vjust=top;
      style=data;
   end;

   define sum;
      generic=on;
      header="Total Dollars Raised";
      vjust=top;
   end;

   define mean;
      generic=on;
      header="Average Dollars per Student";
      vjust=top;
   end;

   define raised;
      generic=on;
      header="Individual Dollars";
   end;

   define name;
      generic=on;
      header="Student";
   end;

   define school;
      generic=on;
      header="School";
   end;

   define year;
      generic=on;
      header="Year";
   end;

   end;
run;

data _null_;
   set top3list;

file print ods = (
          template="means.topn"

columns=(
         class=school(generic=on)
         class=year(generic=on)
         sum=moneyRaised_sum(generic=on)
         mean=moneyRaised_mean(generic=on)
         raised=moneyRaised_1(generic=on)
         raised=moneyRaised_2(generic=on)
         raised=moneyRaised_3(generic=on)
         name=name_1(generic=on)
         name=name_2(generic=on)
         name=name_3(generic=on)
         school=school_1(generic=on)
         school=school_2(generic=on)
         school=school_3(generic=on)
         year=year_1(generic=on)
         year=year_2(generic=on)
         year=year_3(generic=on)
         )
      );

put _ods_;
run;

proc template;
delete means.topn;
run;

Program Description

Set the SAS system options. The OPTIONS statement controls several aspects of the LISTING output.

options nodate pageno=1 pagesize=60 linesize=72;

Create the table template Means.Topn The DEFINE statement creates the table template Means.Topn in the first template store in the path for which you have Write access. By default, this template store is Sasuser.Templat.

proc template;
   define table means.topn;

Specify the symbols that reference three macro variables. The MVAR statement defines three symbols that reference macro variables. ODS will use the values of these variables as strings. References to the macro variables are resolved when ODS binds the template and the data component to produce an output object. First_Year and Last_Year will contain the values of the first and last years for which there are data. Their values are assigned by the SYMPUT statements in the DATA step. SYSDATE9 is an automatic macro variable whose value is always available.

   mvar first_year last_year sysdate9;

Specify the column names and the order in which they appear in the report. The COLUMN statement declares these variables as columns in the table and specifies their order in the table. If a column name appears in parentheses, then PROC TEMPLATE stacks the values of all variables that use that column template one below the other in the output object. Variables are assigned a column template in the DATA step that appears later in the program.

   column class sum mean (raised) (name) (school) (year);

Specify three customized changes to the table template. These three table attributes affect the presentation of the output object in the LISTING output. They have no effect on its presentation in the HTML output. DOUBLE_SPACE= creates double spaces between the rows of the output object. OVERLINE= and UNDERLINE= draw a continuous line before the first row of the table and after the last row of the table.

    double_space=on;
    overline=on;
    underline=on;

Specify the two table headers and the order in which they appear in the report. The HEADER statement declares Table_Header_1 and Table_Header_2 as headers in the table and specifies the order in which the headers appear in the output object.

   header table_header_1 table_header_2;

Create the table element Table_Header_1. The DEFINE statement and its substatement and attribute define Table_Header_1. The TEXT statement specifies the text of the header. The STYLE= attribute alters the style element that displays the table header. The style element Header is defined in the default style, Styles.HTMLBlue. In this case, the STYLE= attribute specifies a large font size. All other attributes that are included in Header remain in effect. This attribute affects only the HTML output. The END statement ends the header template.

   define table_header_1;
      text "Top Three Fund Raisers";
      style=header{fontsize=6};
   end;

Create the table element Table_Header_2. The DEFINE statement and its substatement and attribute define Table_Header_2. The TEXT statement uses text and the macro variables First_Year and Last_Year to specify the contents of the header. When ODS binds the data component to the table template (in the DATA step that follows), it will resolve the values of the macro variables First_Year and Last_Year. The table template itself contains references to the macro variables. The SPACE= attribute inserts a blank line after the header (in the LISTING output only). The END statement ends the header template.

  define table_header_2;
      text "from " first_year " to " last_year;
      space=1;
  end;

Create the table element Table_Footer. The DEFINE statement and its substatement and attribute define Table_Footer. The FOOTER argument declares Table_Footer as a footer. (Compare this approach with the creation of the headers. You could use a FOOTER statement instead of the FOOTER argument in the DEFINE statement.) The TEXT statement specifies the text of the footer. When ODS binds the data component to the table template (in the DATA step that follows), it will resolve the value of the macro variable SYSDATE9. The table template itself contains a reference to the macro variable. The SPLIT= attribute specifies the asterisk as the split character. This prevents the header from splitting at the open parenthesis. If no split character is specified, then ODS interprets the nonalphabetic, leading character as the split character. Alternatively, place a space character before the open parenthesis. The STYLE= attribute alters the style element that displays the table footer. The style element Header is defined in the default style, Styles.Default. In this case, the STYLE= attribute specifies a small font size. All other attributes that are included in Footer remain in effect. This attribute affects only the HTML output. The END statement ends the footer template.

   define footer table_footer;
      text "(report generated on " sysdate9 ")";
      split="*";
      style=header{fontsize=2};
   end;

Create the column template Class. The DEFINE statement and its attributes create the column template Class. (The COLUMN statement earlier in the program declared Class as a column.) GENERIC= specifies that multiple variables can use the same column template. GENERIC= is not specific to a destination.ID= specifies that this column should be repeated on every data panel if the report uses multiple data panels. ID= affects only the LISTING output. VJUST= specifies that the text appear at the top of the HTML table cell that it is in. VJUST= affects only the HTML output. STYLE= specifies that the column uses the DATA table element. This table element is defined in the default style, which is the style that is being used. STYLE= affects only the HTML output. The END statement ends the template. Notice that, unlike subsequent column templates, this column template does not include a header. This is because the same header is not appropriate for all the variables that use this column template. Because there is no header specified here or in the FILE statement, the header comes from the label that was assigned to the variable in the DATA step.

   define class;
      generic=on;
      id=on;
      vjust=top;
      style=data;
   end;

Create six additional columns. Each of these DEFINE statements and its attributes creates a column template. GENERIC= specifies that multiple variables can use a column template (although in the case of Sum and Mean, only one variable uses the template). HEADER= specifies the text for the column header. VJUST= specifies that the text appear at the top of the HTML table cell that it is in. The END statement ends the template.

   define sum;
      generic=on;
      header="Total Dollars Raised";
      vjust=top;
   end;

   define mean;
      generic=on;
      header="Average Dollars per Student";
      vjust=top;
   end;

   define raised;
      generic=on;
      header="Individual Dollars";
   end;

   define name;
      generic=on;
      header="Student";
   end;

   define school;
      generic=on;
      header="School";
   end;

   define year;
      generic=on;
      header="Year";
   end;

End the table template. This END statement ends the table template. The RUN statement ends the PROC TEMPLATE step.

   end;
run;

Create the data component. This DATA step does not create a data set. Instead, it creates a data component and, eventually, an output object. The SET statement reads the data set TOP3LIST that was created with PROC MEANS.

data _null_;
   set top3list;

Route the DATA step results to ODS and use the Means.Topn table template. The combination of the fileref PRINT and the ODS option in the FILE statement routes the results of the DATA step to ODS. The TEMPLATE= suboption tells ODS to use the table template named Means.Topn, which was previously created with PROC TEMPLATE.

file print ods = (
          template="means.topn"

Specify the column template to use for each variable. The COLUMNS= suboption places DATA step variables into columns that are defined in the table template. For example, the first column-specification specifies that the first column of the output object contains the values of the variable SCHOOL and that it uses the column template named Class. GENERIC= must be set to ON in both the table template and each column assignment in order for multiple variables to use the same column template.

columns=(
         class=school(generic=on)
         class=year(generic=on)
         sum=moneyRaised_sum(generic=on)
         mean=moneyRaised_mean(generic=on)
         raised=moneyRaised_1(generic=on)
         raised=moneyRaised_2(generic=on)
         raised=moneyRaised_3(generic=on)
         name=name_1(generic=on)
         name=name_2(generic=on)
         name=name_3(generic=on)
         school=school_1(generic=on)
         school=school_2(generic=on)
         school=school_3(generic=on)
         year=year_1(generic=on)
         year=year_2(generic=on)
         year=year_3(generic=on)
         )
      );

Write the data values to the data component. The _ODS_ option and the PUT statement write the data values for all columns to the data component.

put _ods_;
run;

Remove the customized means table template.The DELETE statement removes the customized means table that was created in this example. When using the DELETE statement, ODS looks for means.topn in Sasuser.Templat and Work.Templat first. If it is there, it will delete it. If not, it will search Sashelp.Tmplmst.

proc template;
delete means.topn;
run;

HTML Output: Using a Customized Table for the TopN Report

HTML Output for the TopN Report (Viewed with Microsoft Internet Explorer)