SAS Warning: CREATE TABLE statement recursively references the target table

Question

SAS allows creation of proc sql create table statement where the table to be created recursively references itself in the select statement e.g.:

proc sql;
     create table t1 as 
     select 
         t1.id
         ,t2.val1 
     from 
         t1
         inner join t2 on t1.id=t2.id
;
quit;

When a statement like this is executed a warning message is written to the log.

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

This warning message could be suppressed by using undo_policy=none option. (see SAS Usage Note 12062)

Question:

• Can creating a table in such a recursive manner potentially return some unexpected results? Is it possible that it would create different results that spiting the same operation into 2 steps:

   proc sql;
     create table _data_ as 
       select 
         t1.id
         ,t2.val1 
       from 
         t1
         inner join t2 on t1.id=t2.id;
  
       create table t1 as
         select * from &syslast;
  
  quit;
  

• Is the two step approach better/safer to use?

Answer 1

I have found that using the same table name as input and output for SAS proc sql can produce incorrect results. It works OK most of the time, but definitely not 100% of the time. Rather than suppress the warning, use a different output table name.

SAS has confessed to this: http://support.sas.com/kb/12/062.html

Answer 2

This should work fine if the tables being queried are SAS datasets. It is no worse than this simple data step.

data t1;
 merge t1 t2;
 by id;
run;

When SAS runs that type of step it will first create a new physical file with the results and only after the step has finished it will delete the old t1.sas7bdat and rename the temporary file to t1.sas7bdat. If you do with a PROC SQL statement SAS will follow the same basic steps.

I believe that the warning is there because if the tables being referenced were from a external database system (such as Oracle) then SAS might push the query into the database and there it could cause trouble.