UNDO_POLICY=NONE update warning - SAS warnings are a Nanny state policy! :>

[pmg7670]

How do I avoid the update warning (see below) which is due to the Nanny state that is SAS

The warning appears in the log when UNDO_POLICY=NONE but with OPTIONAL there is no log message but the program status does not report : successful. I need status = successful

Most sas people I talk to just resign themselves to a non-success output status for an actual valid result

I've investigated the background of the UNDO_POLICY and it is appears more relevent for integrity constraint protection (which is overkill for my program)

  %let keepVars = correlationId t_s_date p_effTimestamp t_end_date policyCoverageId txnType
           coverageAction coverageType brand in_channel occupationCode occupationWages
             pds state termDays;
   
 DATA ciwork.normprem_input;
  attrib numWrkCmpCoverage length = 3;
  SET ciwork.perilprem_input (in=a keep=&keepVars); /* i can't
  numWrkCmpCoverage = 0;
 RUN;

 PROC SQL UNDO_POLICY=OPTIONAL;
  UPDATE ciwork.normprem_input AS a
    SET numWrkCmpCoverage = COALESCE((SELECT b.numCover
    FROM (SELECT correlationId, count(correlationId) as numCover
    FROM ciwork.perilprem_input (WHERE=(coverageType = 'WORKERSCMP'))
   GROUP BY correlationId) AS b
  WHERE a.correlationId = b.correlationId),0);

 QUIT;

WARNING: The SQL option UNDO_POLICY=REQUIRED is not in effect. If an error is detected when processing this UPDATE statement, that
         error will not cause the entire statement to fail.

[Patrick]

I can't replicate this warning with below code. How does this look in your environment?

If the code doesn't throw a warning in your environment as well then can you please try and provide as with some sample code which creates the warning (something we can actually execute and replicate what you describe).

Also: Which OS and SAS version?

data have;
  set sashelp.class;
run;

proc sql UNDO_POLICY=OPTIONAL;
  update have as a
    set sex=(select '?' from sashelp.class as b where a.name=b.name)
  ;
quit;

I consider warnings in general as a good thing as mostly they indicate that something needs attention. I do agree that there are cases where we should be able to explicitly suppress a warning as we fully understand what's happening.

[pmg7670]

Hi,

My Sas package is Foundation

My sas version is : NOTE: SAS (r) Proprietary Software 9.3 (TS1M1) 

My Sas platform is : NOTE: This session is executing on the W32_ES08R2  platform.

Since my code next is similar to my first post, the error mesages appear (see color text)

There is another warning produced which is due to the source and output dataset being the same (i can easily avoid this one)

data devtmp.help_class;

 attrib count_gender length = 3;

 set sashelp.class;

  count_gender = 0;

run;

proc sql UNDO_POLICY=OPTIONAL;

 update devtmp.help_class AS a

 set count_gender = (select b.count_gender

     from (select name, count(sex) AS count_gender

                from devtmp.help_class

                group by sex) AS b

 where a.name = b.name);

quit;

190 proc sql UNDO_POLICY=OPTIONAL;

191 update devtmp.help_class AS a

192 set count_gender = (select b.count_gender

193 from (select name, count(sex) AS count_gender

194 from devtmp.help_class

195 group by sex) AS b

196 where a.name = b.name);

WARNING: A value expression of the SET clause references the data set being updated.

NOTE: The query requires remerging summary statistics back with the original data.

WARNING: The SQL option UNDO_POLICY=REQUIRED is not in effect. If an error is detected when

processing this UPDATE statement, that error will not cause the entire statement to fail.

NOTE: 19 rows were updated in DEVTMP.HELP_CLASS.

197 quit;