IMSTAT Procedure

Example 5: Creating a Star Schema

Details

The following example demonstrates using the SCHEMA statement to join dimension tables with a fact table.

Program

libname example sasiola host="grid001.example.com" port=10010 tag='hps';

proc imstat;
   table example.mailorder; 1
   schema catalog  (catCode=CatCode)
          products (pcode  =pcode  )
          customers(custnum=custnum); 2
run;

   table example.&_templast_
run;
   columninfo;
quit;

Program Description

  1. Once the WHERE clause is specified, it applies to the statements that follow it. It also crosses RUN boundaries.
  2. The SAVE statement is subject to the WHERE clause. As a result, the records from the Prdsale table that meet the WHERE clause are saved to /dept/sales/y1994q1.sashdat. The FULLPATH option is used to specify the table name instead of using the name of the active table. This is particularly useful when saving temporary tables.
  3. The DELETEROWS statement is also subject to the WHERE clause. The records that were just saved to HDFS are now deleted and purged from memory. (The DELETEROWS statement without the PURGE option would mark the records for deletion and exclude them from being used in calculations, but it does not free the memory resources.)
  4. The WHERE clause is cleared and the SUMMARY statement that follows is performed against all the remaining records in the Prdsale table.

Output

The following output shows the temporary table name and how the dimension table names are used as prefixes for the column names.
Viewing column information for a table that uses the SCHEMA statement
Copyright © SAS Institute Inc. All rights reserved.