BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cellurl
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

cellurl
Quartz | Level 8

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.

ballardw
Super User

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.

cellurl
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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:

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 23140 views
  • 4 likes
  • 4 in conversation