Creating custom reports from the Data Mart

My previous blog discussed the SAS Environment Manager Report Center, and talked about its organization, and how to start using some of the prompts to get the reports you want. The next step is to learn to use some of the example reports provided to help you design your own, production-level reports.

It’s helpful to distinguish ad-hoc, or exploratory reporting, vs. standardized, production-style reports, which might be run daily, weekly, or any other number of times. You can experiment with many ad-hoc reports and create hundreds of variations and different reports “on the fly,” just by manipulating the various parameters provided within the Report Center interface. Perhaps more importantly, you can use knowledge gained from this process to design more permanent production reports, using some of the provided examples as “templates.”

First, you need to understand the basic structure: All the SAS Environment Manager Reports are SAS Stored Processes available from the Stored Process web application. Most of them accept user prompts that specify many details about the data to include and the way the output should appear. Then, the stored process passes these parameters to a small set of reporting macros, which perform the data manipulations and produce the actual report using well-known SAS Procedures.

In addition to all the pre-defined reports, there are two sets of example reports specifically intended to be used as templates from which you can develop your own custom report. Often, this only requires a few changes to parameters being passed to the reporting macro. These example reports are located here:

Under Products>SAS Environment Manager>Custom
Under System>Applications>SAS Environment Manager>Ad Hoc Reporting

In the first set, the Example2 Call EV Macros with prompts is likely of most interest. This report demonstrates how to call two of the most useful reporting macros:

%evtmpplate creates a wide variety of plots using PROC TEMPLATE
%evtabrpt  creates a wide variety of data tables using PROC TABULATE

The second set in Ad Hoc Reporting has a series of reports that use many additional reporting macros. Notice that many of the reports include the reporting macro name they showcase, which matches the corresponding stored process filename, in the report name itself. Thus you can quickly locate the most important files being used in the running of the report based on the report’s name. Here’s a table showing the most useful reporting macros:

Armed with this information, here’s the approach to take to create your own custom reports in the Report Center:

Decide what you want to report on, and what general type of report you want to create (table, crosstab, bar chart, line graph, heat map, etc.)

Try to find existing reports in the Report Center that may already do what you want, or are at least based on the data that you want to use

Determine which Data Mart data set you want to report from, based on Steps 1 and 2

If none of the existing reports provide what you want, try experimenting with all the parameters available in the Ad-Hoc reports section, (ACM, ARTIFACT, KITS libraries only) using the report type you want (plot, chart, tabulate, etc.) to determine the example report to try.　 Most of these reports allow you a wide variety of choices, including:
o  data set
o  main analysis variables
o  class variables, or BY variables
o  type of plot, plus many options and details about the plots
o  data subsetting by variable (WHERE clause), dates
o  output formatting
o  titles/footnotes/ODS style, etc.

When running the Ad Hoc reports, first specify the data set and analysis variable(s) that you want, and then experiment with various other parameters. Open the file containing the macro code that　you are using–the reporting macros are fully documented with all options and parameters accepted. Use　the table above to help you find the correct file(s).

Once you get the report that you want from the Ad Hoc section, then you can use the information you’ve gained to create a more permanent version. Here’s an overview of the steps to create a permanent custom report:

Caution before proceeding: Be careful not to modify any of the stored process files that are part of the Report Center. If you want to modify one of the stored processes, first make a copy and give it a new name. Then simply refer to the new name in that stored process’ metadata and modify the copy.  Or, you can put the stored process code directly in metadata instead of a file, in which case you don’t have to alter any Report Center files.

Using SAS MC, copy the existing Ad Hoc report to a new metadata location, and rename it.

Make a copy of the stored process file called by the report, and rename it.

Edit the new (copied, renamed) stored process code, and you will see the reporting macro(s) being called.

Modify the parameters on any called reporting macros with the new parameters you want, based on what you learned in the previous steps.  For more guidance, open the *.sas file containing the reporting macro, and follow the detailed documentation that’s included with each macro.

Chances are that it will require a few tries before you get it exactly right, but it’s nice to see how much of the work has already been done for you with the existing examples.

More information on the steps to add a report to Report Center.

Creating custom reports from the Data Mart was published on SAS Users.