NOTE: (Constructive) Feedback is Good

Adding the FEEDBACK keyword to a PROC SQL statement will result in a bunch of useful information being written to the SAS log:

• Any asterisk (for example, SELECT *) is expanded into the list of qualified columns that it represents. 
• Any PROC SQL view is expanded into the underlying query. 
• Macro variables are resolved. 
• Parentheses are shown around all expressions to further indicate their order of evaluation. 
• Comments are removed.

Here’s an example log, showing the SELECT * being expanded and a macro variable being fully resolved (note how the column name is prefixed with the table name, and the sort order (ASC) is shown):


15 %let ob = age; 
16 proc sql feedback; 
17   select * 
18     from sashelp.class 
19     order by &ob 
20     ; 
NOTE: Statement transforms to: 


        select CLASS.Name, CLASS.Sex, CLASS.Age, CLASS.Height, CLASS.Weight 
          from SASHELP.CLASS 
      order by CLASS.Age asc; 


 21 quit; 

Quite apart from revealing some of what SAS is doing behind the scenes, the expansion of SELECT * into the log allows you to subsequently copy/paste the column names back into your program and then remove one or two that you don’t want to keep.

Another good option for revealing information is OPTION MSGLEVEL=I. Setting this option will also result in some hitherto “secret” information being written to the SAS log. For example, SAS will tell you if it has used an index (and it will tell you which index it has used). Here’s an example log (demonstrating how indexed data sets can be successfully read through a BY statement even if the data set is not physically sorted):

15 option msglevel=i; 
16 data holiday; 
17   set maps.spain2; 
18   by regname; 
19 run; 
INFO: Index REGNAME selected for BY clause processing. 
NOTE: There were 53 observations read from the data set MAPS.SPAIN2. 
NOTE: The data set WORK.HOLIDAY has 53 observations and 10 variables.

The FEEDBACK and MSGLEVEL options are both useful techniques to get more of an insight into what SAS is doing and hence what your program is doing.