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

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

cellurl — Fri, 03 Oct 2014 13:51:52 GMT

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

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

Ksharp — Fri, 03 Oct 2014 13:56:54 GMT

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

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

RW9 — Fri, 03 Oct 2014 14:04:49 GMT

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;

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

cellurl — Fri, 03 Oct 2014 15:05:59 GMT

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.

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

ballardw — Fri, 03 Oct 2014 15:28:04 GMT

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.

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

cellurl — Fri, 03 Oct 2014 15:52:20 GMT

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".....

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

RW9 — Fri, 03 Oct 2014 15:58:05 GMT

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: