Feedback Option

From sasCommunity

--EvilPettingZoo97 00:29, 06 July 2007 (EDT)

When the FEEDBACK option on the PROC SQL statement is specified an explicit version of the query is written in the SAS log. The following terse notations and expressions are expanded in the re-written version of the query:

- Any macro variables included in the query are resolved, regardless of whether the SYMBOLGEN option is specified.

- Columns implicitly referenced by the SELECT * (select all) statement

- The join conditions involved with a NATURAL JOIN

- The CONTAINS operator specified with '?'

- The AND and OR logical operators written as '&' and '|', respectively

- Parenthesis are added to show the grouping of operations

- Expansion of any SQL views involved in the query (note, views created with a DATA step are not expanded)

- '<>' is replaced by 'not ='

- One level specified table and views names are augmented with the WORK libref

The FEEDBACK option can be helpful to new PROC SQL and SAS users that are not privy to various short-cut notations or to those debugging a succinct version of a query. The default setting for PROC SQL is NOFEEDBACK.

[edit] Example

Split the Sashelp.Class table into two parts.

proc sql ;
 create table part1 as
 select  *
 from    Sashelp.Class( drop=weight ) ;

 create view part2 as
 select  name, weight
 from    Sashelp.Class ;

 quit ;

With the FEEDBACK option specified, piece the original table back together and apply some subsetting criteria.

%let letter=a ;

proc sql feedback ;
 create table class as
 select  *
 from    part1 as T1
         natural inner join
         part2
 where   name ? "&letter." &
         weight le 15 | height <> 50  ;
 quit ;

Re-written version of the query in the SAS log

       select COALESCE(T1.Name, CLASS.Name) as Name, CLASS.Weight, T1.Sex, T1.Age, T1.Height
         from WORK.PART1 T1 inner join
              ( select CLASS.Name, CLASS.Weight
                  from SASHELP.CLASS
              ) on T1.Name=CLASS.Name
        where (CLASS.Name contains 'a' and (CLASS.Weight<=15)) or (T1.Height not = 50);