In View mode, report viewers who have the Basic Edit capability can view details about the data source that is being used by the current report section, including information about the data items. However, users who do not have this capability cannot see this information. One solution is to insert a text object, and manually enter and maintain data item information. However, a more automated way is to take advantage of the SQL DICTIONARY table for the relational information map (or maps) used in the report.
This sample explains how to extract data item names, descriptions, and classifications from the SQL DICTIONARY table for an information map and save that data in a table. It then explains how to create a stored process that can be used to display that information in a report like the one shown in Figure 1.
Figure 1. The stored process output on the left includes all of the data items in the information map, not just those used in the table.
This sample uses a map named shoesmap
, which is based on a copy of the SASHELP.SHOES table. You can create the information map by submitting the code on the Full Code tab, or you can follow along using your own information map.
This sample was written using SAS 9.3 M1 and SAS Web Report Studio 4.31 M1. SAS 9.2 and SAS Web Report Studio 4.2 are the earliest versions that can be used with this sample.
You can submit the code in this sample in an interactive Base® SAS session or in SAS® Enterprise Guide®.
The following sample code extracts data item names, classification information (measure or category), and descriptions from the shoesmap
information map. This information is saved in a new table named map
.
/* Specify the metadata location of your information map */
%let mpath=/Shared Data/your-map-folder;
/* Enter your metadata-specific information */
libname mymaps infomaps
metauser="your-userID"
metapass="your-password"
metaserver="your-metadata-server.com"
metaport=8561
metarepository=Foundation
mappath="&mpath";
/* Submit a LIBNAME statement for your physical data store */
LIBNAME yourlib BASE "C:\SAS\EBI\Lev1\Data";
/* Extract data item information from the */
/* SQL DICTIONARY table for the shoesmap. */
/* Save the information in a table named "map" */
proc sql;
create table yourlib.map as
select d.dataitemname, d.class, d.description
from DICTIONARY.DATAITEMS as d
where d.memname="shoesmap";
quit;
|
Note: If you modify the information map to perform tasks such as deleting data items, adding data items, or changing data item descriptions, resubmit the previous code in order to update the table.
The following stored process code generates the output shown on the right in Figure 1.
*ProcessBody;
%STPBEGIN;
OPTIONS VALIDVARNAME=ANY;
/* Submit the LIBNAME statement to the location of the "map" table */
LIBNAME yourlib BASE "C:\SAS\EBI\Lev1\Data";
PROC SORT
DATA=yourlib.MAP(KEEP=dataitemname description class)
OUT=WORK.SORTTempTableSorted
;
BY class;
RUN;
TITLE;
TITLE1 "Data items available for use in the report";
FOOTNOTE;
PROC PRINT DATA=WORK.SORTTempTableSorted
NOOBS
LABEL
ROWS=PAGE
;
VAR dataitemname description;
BY class;
RUN;
%STPEND;
|
In SAS® Management Console, register the stored process in metadata. For detailed steps for your version of SAS Management Console, select Help ► Help on SAS Management Console. On the Contents tab, navigate to Stored Process Management ► Register a New Stored Process.
Figure 2 shows what the Execution properties for the stored process might look like in SAS Management Console 9.3.
Figure 2. Execution properties for the stored process as defined in SAS Management Console 9.3.
Note: Options such as selecting a default server and saving the source code in metadata are new in SAS 9.3.
In SAS Web Report Studio, select the shoesmap
as the data source, insert an object such as a crosstabulation table to display the results, and then insert the stored process that displays the information about the information map.
You can include the stored process in the same report section that uses data from the information map, as shown in Figure 1, or you can display the output in a separate section.
For example, if you have multiple report sections, each using a different information map, you might want to create one report section that displays output from multiple stored processes, each displaying data item information about one of the information maps in the report. In each stored process, use the TITLE1 statement to include the name of the related information map or the name of the report section (for example, one title might be: "Data items used in Section 1").
For another example of how to use information map SQL DICTIONARY tables for reporting, see Reporting on SAS Information Maps from The SAS Dummy: A blog for the rest of us.
For information about the SQL DICTIONARY tables for use with the SAS Information Maps LIBNAME Engine, see SQL Dictionary Tables for the Information Maps Engine.
For more information about SAS Web Report Studio, see the SAS Web Report Studio documentation page.
For more information about SAS Management Console, see the SAS Management Console documentation page.
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.
Before submitting this code, these tasks must be done:
Note: For information about registering SAS libraries and tables, see Establishing Connectivity to a Library of SAS Data Sets.
%let mpath=/Shared Data/your-map-folder;
proc infomaps metauser="your-userID"
metapass="your-password"
metaserver="your-metadata-server.com"
metaport=8561
metarepository=Foundation
mappath="&mpath";
new infomap "shoesmap" auto_replace=yes;
insert datasource
sasserver="SASApp"
table="yourlib".shoes
id="shoes";
insert dataitem
column="shoes"."Stores"
desc="Number of stores in the region that sell a product";
insert dataitem
column="shoes"."Product"
desc="The name of the product as it appears in the sales catalog.";
insert dataitem
column="shoes"."Region"
desc="The name of the region where the product is sold.";
insert dataitem
column="shoes"."Sales"
desc="Total amount of product sales for each region.";
save;
* list _all_;
run;
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.
Type: | Sample |
Date Modified: | 2012-02-08 16:38:17 |
Date Created: | 2012-01-27 12:10:57 |
Product Family | Product | Host | Product Release | SAS Release | ||
Starting | Ending | Starting | Ending | |||
SAS System | SAS Web Report Studio | Microsoft® Windows® for x64 | 4.2 | 9.2 TS2M0 | ||
Microsoft Windows Server 2003 Datacenter Edition | 4.2 | 9.2 TS2M0 | ||||
Microsoft Windows Server 2003 Enterprise Edition | 4.2 | 9.2 TS2M0 | ||||
Microsoft Windows Server 2003 Standard Edition | 4.2 | 9.2 TS2M0 | ||||
Microsoft Windows Server 2003 for x64 | 4.2 | 9.2 TS2M0 | ||||
Microsoft Windows Server 2008 for x64 | 4.2 | 9.2 TS2M0 | ||||
Microsoft Windows XP Professional | 4.2 | 9.2 TS2M0 | ||||
Windows Vista | 4.2 | 9.2 TS2M0 | ||||
Windows Vista for x64 | 4.2 | 9.2 TS2M0 | ||||
64-bit Enabled AIX | 4.2 | 9.2 TS2M0 | ||||
64-bit Enabled Solaris | 4.2 | 9.2 TS2M0 | ||||
HP-UX IPF | 4.2 | 9.2 TS2M0 | ||||
Linux for x64 | 4.2 | 9.2 TS2M0 | ||||
Solaris for x64 | 4.2 | 9.2 TS2M0 |