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

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

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

Should I worry about this warning?

I realize it appears to be bad-practice, but

Should I worry?

Thanks


MPRINT(CUSTOMER):   proc sql;

MPRINT(CUSTOMER):   create table tmp as select * from tmp;

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

         problem.

NOTE: Table WORK.TMP created, with 124698 rows and 1 columns.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.54 seconds

      cpu time            0.66 seconds


Accepted Solutions
Solution
‎10-03-2014 09:56 AM
Super User
Posts: 9,878

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

Yes. You should worry . It seems you need change some tables name .

View solution in original post


All Replies
Solution
‎10-03-2014 09:56 AM
Super User
Posts: 9,878

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

Yes. You should worry . It seems you need change some tables name .

Super User
Super User
Posts: 7,727

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

Note that once done, the above is final and the dataset you already had will be irrevocably changed!.

The warninig comes about due to you writing a table in your create table statement which is of the same name as the you are selecting the data from, hence overwriting the read in table.  This is fine if that is indeed what you intended to do.  See: 12062 - "Warning: CREATE TABLE statement recursively references the target table"

You can use the

proc sql  undo_policy=none;

to get rid of this.

I would again point out that you need to be clear on what you are doing and in general the process would be to create a new table or view based of existing data.  There are other options to this, for instance you can update existing tables.  So if you want to change tmp do:
proc sql;

     update tmp

     set calc_var=1

     where     something is true;

quit;

Contributor
Posts: 46

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

So we all benefit... Maybe I am just lucky.....

I just got an answer from our 20 year SAS veteran here.

He says, "Never known to cause actual problems".

Thanks everyone.

Super User
Posts: 11,139

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

Just don't use

proc sql;

     create table temp as select onevar from temp;

quit;

Unless that table only had one variable, you just lost all but the one variable. Of course the warning comes too late...

For similar concerns I dislike use of:

Data temp;

     set temp;

structures.

I inherited some code like this and found a section that was recoding a variable with values of 1 and 2 to 1, 3 to 2, 4 to 3. The previous user had run the same code a couple times and ended up with almost all values of 1.

Contributor
Posts: 46

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

my 20 year vet DID just now tell me "get rid of warnings".

Soo, when he said its not a problem, its because he gets rid of all warnings.

"Do what the teacher says".....

Super User
Super User
Posts: 7,727

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

Trust me, once you have accidentally removed 6 years of edit check output in Oracle and spend quite a long time trying to rebuild it you won't be so blase about warnings/errors :smileyshocked:

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 2795 views
  • 0 likes
  • 4 in conversation