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

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

pmg7670 — Tue, 12 Jul 2016 01:05:27 GMT

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.

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

Patrick — Tue, 12 Jul 2016 12:19:04 GMT

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.

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

pmg7670 — Tue, 12 Jul 2016 23:05:10 GMT

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;