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?

Having no idea what results you would "expect" from this invalid query, it is hard to say whether it could return "unexpected" results. SAS proc sql doesn't support recursive queries. — Gordon Linoff
@GordonLinoff - from what I have seen so far it produces the same output as the two step approach, I am not sure if the results are deterministic or not. I have inherited some sas code and I am trying to make sense of it. — kristof
I THINK this depends on the engine used. The BASE engine (what you are using in the WORK library) creates a temporary data set behind the scenes and then copies it over at the end. Other engines may not do that, hence the WARNING. — DomPazz
I have gotten this warning as well, but I have yet to see any anomalous results. The developers must know of very specific cases that bad results could be returned, and place that disclaimer in just in case. I tried making some of the worst recursive sql statements possible, but couldn't get wrong results. — Stu Sztukowski
Personally, I would avoid it. It might work 99.9999% of the time, but if you're working with critical data, you want to be absolutely certain your read/write operations will work 100% of the time. If you would like to do this, I would recommend a data step. Data steps do support these recursive update operations and such. If something does go wrong in the future, even if the sql statement is not the exact reason for a fault, it's easier to explain to your boss than "I ignored this warning here, but that's probably unrelated" ;) — Stu Sztukowski

Tom Tom · Accepted Answer · 2016-02-15T23:28:19

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.

SAS Warning: CREATE TABLE statement recursively references the target table

2 Answers