8.5. Hiding Logic Complexities

Because complex logic constructs such as multi-way table joins, subqueries, or hard-to-understand data relationships may be beyond the skill of other stuff in your area, you may want to build or customize views so that others can access the information easily. The next example illustrates how a complex query containing a two-way join is constructed and saved as a view to simplify its use by other users.

SQL Code

PROC SQL;
  CREATE VIEW PROD_MANF_VIEW AS
    SELECT DISTINCT SUM(prodcost) FORMAT=DOLLAR10.2,
           M.manunum,
           M.manuname
      FROM PRODUCTS AS P, MANUFACTURERS AS M
        WHERE P.manunum  = M.manunum AND
              M.manuname = 'KPL Enterprises';
QUIT;

SAS Log Results

   PROC SQL;
     CREATE VIEW PROD_MANF_VIEW AS
       SELECT DISTINCT SUM(prodcost)
 FORMAT=DOLLAR10.2,
              M.manunum,
              M.manuname
         FROM PRODUCTS AS P, MANUFACTURERS AS M
           WHERE P.manunum  = M.manunum AND
                 M.manuname = 'KPL Enterprises';
NOTE: SQL view WORK.PROD_MANF_VIEW has been defined.
   QUIT;
NOTE: PROCEDURE SQL used:
      real time           0.00 seconds

In the next example, the PROD_MANF_VIEW is simply referenced in a SELECT query. Because the view’s SELECT statement references the product cost (PRODCOST) column with a summary function but does not contain a GROUP BY clause, the note "The query requires remerging summary statistics back with the original data." appears in the SAS log below. This situation causes the sum to be calculated and then remerged with each row in the tables being processed.

SQL Code

PROC SQL;
  SELECT *
    FROM PROD_MANF_VIEW;
QUIT;

SAS Log Results

  PROC SQL;
    SELECT *
     FROM PROD_MANF_VIEW;
NOTE: The query requires remerging summary
 statistics back with the
original data.
   QUIT;
NOTE: PROCEDURE SQL used:
      real time           0.05 seconds

Results

                   The SAS System

              Manufacturer
                    Number   Manufacturer Name
  ____________________________________________________

   $1,296.00           500   KPL Enterprises