Ok so here's the answer I got from SAS Technical Support. (My highlights) ------------------------------------------------------------------------------------------------------------------------------------ Dominic, The query at the bottom of the SAS Note you linked to is a query that was found by development to produce data integrity issues. However, consistently reproducing this behavior is not possible. (here's the problematic code from Note 12062) proc sql undo_policy=none;
delete from one
where id in (select id from one);
quit; A query such as the one below, will not, to our knowledge, produce any data integrity issues: proc sql;
create table x as
select * from x;
quit; The PROC SQL developers continue to work to more closely align with the ANSI standards. As mentioned in the Usage Note, the warning was added to notify users of possible data integrity issues as a result of using recursive table references which are only supported for SQL that is fully ANSI compliant. Due to SAS not being fully compliant with the SQL ANSI standards, in queries which contain recursive table references, if the query were to fail due to a system error outside of PROC SQL, we cannot guarantee the integrity of the input table. Therefore, we are required to produce the Warning based on the query itself. Thanks, (Tech name) SAS Technical Support Cary, NC ------------------------------------------------------------------------------------------------------------------------------------ So basically, what I get out of this is (there's some amount of deduction here so let me know if your interpretations differ): 1. Having a generation dataset does not have any impact on the relevance of the warning - PROC SQL will not use the backups and so corrupt output tables would need to be rolled back manually => proc delete data=mydata(gennum=0); 2. Overwriting by recursion does not pose any problems as long as i. you stay in PROC SQL (which I assume means: not connecting to a remote DBMS via passthru and so on.) ii. nothing is otherwise preventing your query to execute (power outage or computer crash maybe?). 3. Updating / Inserting or removing data in a recursive manner might however fail, some of the time. Hopefully this clarifies the issue at least to a certain extent.
... View more
