topic Re: Bizarre SQL error when building table over itself in SAS Programming

Bizarre SQL error when building table over itself

EvoluZion3 — Mon, 15 Oct 2018 13:44:05 GMT

Hi, I've come across this unexpected behaviour when writing a table over itself when referencing itself. I know this is generally bad practice and I avoid it whenever I can, however it'd be interesting to see what SAS is trying to do when running the below code.

If you run the below code (I know the join isn't complete for the Shoes table, but no matter) it should be successful.

PROC SQL;

  CREATE TABLE WORK.DEV AS SELECT Product FROM SASHELP.SHOES;

  CREATE TABLE WORK.DEV AS
  SELECT
    DEV.Product
  FROM
    WORK.DEV
    LEFT JOIN ( SELECT Product FROM WORK.DEV WHERE 1 = 1 ) NQ
    ON DEV.Product = NQ.Product;
QUIT;

However if you change the "1 = 1" to "1 = 0" and rerun, my SAS returns

"ERROR: Close of OUTPUT file WORK.DEV.DATA failed; another copy of the file is still open for INPUT; file not replaced."

I wouldn't expect this to error (certainly not driven by the trueness/falsehood of the clause).

I'm using EG7.1 and 9.4, but the above doesn't error on EG5.1/9.3 (we've recently upgraded).

Re: Bizarre SQL error when building table over itself

Quentin — Mon, 15 Oct 2018 14:11:18 GMT

Interesting. Replicated on 9.4M3. I wonder if it's a problem of the SQL compiler doing bad short-circuiting when there is an obviously false where clause.

This seems to work okay (ignoring the warning about possibility integrity problems, which seems risky to ignore : ) . I use RANUNI to filter:

PROC SQL;
  CREATE TABLE WORK.DEV AS SELECT Product FROM SASHELP.SHOES;
  CREATE TABLE WORK.DEV AS
  SELECT
    DEV.Product
  FROM
    WORK.DEV
    LEFT JOIN ( SELECT Product FROM WORK.DEV where ranuni(0)<.5 ) NQ
    ON DEV.Product = NQ.Product;
QUIT;

If you change to an expression which will always be false, but the compiler isn't smart enough to know it will always be false, it works:

PROC SQL;
  CREATE TABLE WORK.DEV AS SELECT Product FROM SASHELP.SHOES;
  CREATE TABLE WORK.DEV AS
  SELECT
    DEV.Product
  FROM
    WORK.DEV
    LEFT JOIN ( SELECT Product FROM WORK.DEV where ranuni(0)< -1 ) NQ
    ON DEV.Product = NQ.Product;
QUIT;

I would think specifying obs=0 would be logically equivalent to WHERE 0, but obs=0 does not cause a problem:

PROC SQL;
  CREATE TABLE WORK.DEV AS SELECT Product FROM SASHELP.SHOES;
  CREATE TABLE WORK.DEV AS
  SELECT
    DEV.Product
  FROM
    WORK.DEV
    LEFT JOIN ( SELECT Product FROM WORK.DEV (obs=0)) NQ
    ON DEV.Product = NQ.Product;
QUIT;

Re: Bizarre SQL error when building table over itself

Quentin — Mon, 15 Oct 2018 14:15:03 GMT

You don't actually need a join for this to happen. A simpler test case is:

PROC SQL;
  CREATE TABLE WORK.DEV AS SELECT Product FROM SASHELP.SHOES;

  CREATE TABLE WORK.DEV AS
  SELECT
    DEV.Product
  FROM
    WORK.DEV
  WHERE 0
  ;

QUIT;

Re: Bizarre SQL error when building table over itself

Kurt_Bremser — Mon, 15 Oct 2018 14:21:24 GMT

Good catch. Bring it to the attention of SAS technical support.

Re: Bizarre SQL error when building table over itself

EvoluZion3 — Mon, 15 Oct 2018 14:44:07 GMT

Ok I'll raise this with SAS. Quentin I hope you don't mind if I use the shortened version which you discovered.

This will be the second fault I've found with SAS and it makes me worry about the quality of my SQL haha.

Re: Bizarre SQL error when building table over itself

EvoluZion3 — Mon, 15 Oct 2018 15:49:27 GMT

Apparently this has already been raised:
http://support.sas.com/techsup/notes/v8/12/330.html

Re: Bizarre SQL error when building table over itself

Quentin — Mon, 15 Oct 2018 18:25:55 GMT

Thanks for posting the resolution. I suggest you accept your post with the link to the tech support note as the solution. At least an explanation. : )