Can I stop the "WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem" message when creating a table in PROC SQL?


WARNING and ERROR messages in SAS serve a useful purpose, but there are certain situations where the message is known, anticipated, and it may be deemed acceptable to suppress it.

One such example is the message:

WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem

when writing a SAS dataset back over itself in PROC SQL.

In DATA step syntax, it is not uncommon to generate a dataset, then write back over the dataset in a subsequent process (although some developers prefer not to), e.g.

data boys (drop = sex) ;
  set sashelp.class ;
    where sex = 'M' ;
run ;

data boys ;
  set boys ;
    name = upcase(name) ;
run ;

No messages are written to the LOG advising that the dataset will be altered during the subsequent process.

Performing a comparable process in PROC SQL:

proc sql noprint ;
  create table girls as
    select *
    from sashelp.class
    where sex = 'F'
    ;
quit ;

proc sql noprint ;
  create table girls as 
    select  upcase(name) as name
	   ,age
	   ,height
	   ,weight
    from girls
    ;
quit ;

generates the WARNING message in the LOG. 

The message is generated as a consequence of the default setting of a GLOBAL System Option:

options sqlundopolicy = required | optional | none ;

The possible values denote:

REQUIRED * The system reverses all inserts or updates that have been done to the point of the error.
OPTIONAL The system reverses all inserts or updates that can be reliably reversed.
NONE Keeps any changes made.

* Default action

The disparity between the DATA step and PROC SQL solutions may result in a developer electing to suppress the WARNING message, and this can be done, either by changing the Global System Option, or by over-riding the Global System Option with an option on a PROC SQL statement.

Either of:

options sqlundopolicy = none ;

or

proc sql undo_policy = none ;

will suppress the WARNING. 

As with many such SAS options the statement option takes precedence over the Global option.

 

Author:
Alan D Rudland
Revision:
1.1
Average rating:0 (0 Votes)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.