When you work out, you probably have a routine set of exercises that you do. But if you read health-and-fitness websites or talk to a personal trainer, you know that for optimal fitness, you should vary your workout routine. Not only does adding variety to your fitness regime help you prevent injuries, it also helps build muscles by working different parts of your body. For example, if you’re a runner, you could add weights or another resistance workout once or twice a week. If you are a weight lifter, you might add yoga or Pilates to your repertoire to enhance your personal fitness. In a similar way, it can be beneficial for you as a SAS programmer to vary your coding methods by trying new things.
SAS programmers very often use the TEMPLATE procedure to create style templates that use the CLASS and STYLE statements. I am going to help you bulk up your PROC TEMPLATE muscles by showing you how to use the COMPUTE AS and TRANSLATE INTO statements to alter the structure of tables via table definitions.
The COMPUTE AS statement helps you create or modify columns of data. You can use this statement to create a column from raw data (similar to what you can do with the REPORT procedure), or you can use it with an existing table template. The following example uses a PROC TEMPLATE step that illustrates the former. This example uses an existing data set (the CARS data set that is shipped in the SASHELP library) and computes an average-miles-per-gallon variable called MPG_AVG:
proc template; define table mytable; column make model mpg_city mpg_highway mpg_avg; define mpg_avg; header="Avg MPG"; compute as mean(mpg_city,mpg_highway); format=3.; style={font_weight=bold}; end; define make; blank_dups=on; end; end; run; data _null_; set sashelp.cars(where=(cylinders=4 and drivetrain="All")); file print ods=(template='mytable'); put _ods_; run; |
Here are partial results from the example code:
You’ll notice that I just could not resist sticking with part of my normal PROC TEMPLATE routine when I added a style change to the new column MPG_AVG. I also made the values in that column stand out by using bold font. In addition, because I like to prevent duplicates from appearing, I used the BLANK_DUPS=ON column attribute for the MAKE column.
If you want to try a cool new trick that is available in SAS® 9.4, try the following ODSTABLE procedure. This code does exactly what the previous example does, only with fewer keystrokes. It's similar to combining a strength and cardio move in your exercise routine!
proc odstable data=sashelp.cars(where=(cylinders=4 and drivetrain="All")); column make model mpg_city mpg_highway mpg_avg; define mpg_avg; header="Avg MPG"; compute as mean(mpg_city,mpg_highway); format=3.; style={font_weight=bold}; end; define make; blank_dups=on; end; run; |
Most procedures (except for PRINT, REPORT, and TABULATE) use an existing table template. You can determine the table template that a procedure uses by reviewing the log information that is generated when the ODS TRACE ON statement is in effect. For example, based on the log information from an ODS TRACE ON statement, we know that the following MEANS procedure code uses a table template called BASE.SUMMARY.
proc means data=sashelp.cars; class drivetrain; var mpg_city; run; |
If you submit this PROC MEANS code, you obtain the results shown below:
In the following example, I use the COMPUTE AS statement to edit the MEAN column by multiplying the mean statistic by 10:
proc template; edit base.summary; edit mean compute as (mean * 10); format=6.1; end; end; run; |
Here are the results that are generated when you run the PROC MEANS step a second time (with an explanatory footnote added):
It is important to note that the default location where PROC TEMPLATE stores results is the SASUSER library. Therefore, subsequent PROC MEANS steps also multiply the mean by 10 unless you delete this table template. Just like a personal trainer recommends stretching after each workout in order to avoid sore muscles, I recommend the following PROC TEMPLATE step to delete the updated table template after the PROC MEANS step:
proc template; delete base.summary; run; |
The COMPUTE AS statement is useful when you need to create or edit an entire column. However, in many of the statistical procedures, the reported statistics are stacked. Technical Support has received requests from SAS programmers to make changes to just one of the statistics in a column. Therefore, when you want to change a single cell in a table, I recommend that you exercise the TRANSLATE INTO statement.
Consider the following code in which the Parameter Estimates object is requested from the RELIABILITY procedure:
ods select ParmEst; proc reliability data=fan; distribution Weibull; pplot lifetime*censor( 1 ) / covb; run; |
Note: The DATA=FAN option in the code above links to the online documentation where you can find the DATA step that builds the data set FAN.
The code above displays the results shown in this table:
In this table, the Estimate column displays the parameter estimates. If you need to modify just one set of parameters (for example, just the Extreme Value [EV] parameter estimates), you can use the TRANSLATE INTO statement to do the job.
The TRANSLATE INTO statement applies conditional logic to effectively change the formatting of the Estimate column only for the EV Location and EV Scale parameters. In the following example, the Estimate column for the EV Location parameter is displayed with the 10.5 format. In addition, the EV Scale parameter is displayed with a 10.6 format, while the remaining cells are displayed with the default format applied, which is specified as 10.4.
proc template; edit qc.reliability.ppest; edit estimate; header="Estimate"; format=10.4; translate (Parameter = 'EV Location') into put(_val_, 10.5), (Parameter = 'EV Scale') into put(_val_, 10.6); end; end; run; |
When you rerun the PROC RELIABILITY step that is shown above, you get these results:
The changes that you make to your statistics can be much more dramatic. Just like with your workout, the more effort you put into it, the more defined your results!
The samples and SAS Notes found on support.sas.com focus on the DEFINE, STYLE, and CLASS statements in PROC TEMPLATE. These statements are fabulous tools in the PROC TEMPLATE routine that enhance your ODS results because they help change the style of your Excel, HTML, RTF, and PDF results. By adding the COMPUTE AS and TRANSLATE INTO statements to your tools, you can round out your ODS routine to further enhance your ODS results. But if you find that you need the help of a personal trainer (that is, a Technical Support consultant), call Technical Support or send email to support@sas.com!
Are you ready to enhance your PROC TEMPLATE programming routine even more? If so, here are some additional resources:
