07-11-2016 09:05 PM
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;
attrib numWrkCmpCoverage length = 3;
SET ciwork.perilprem_input (in=a keep=&keepVars); /* i can't
numWrkCmpCoverage = 0;
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);
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.
07-12-2016 08:17 AM - edited 07-12-2016 08:19 AM
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.
07-12-2016 07:05 PM
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)
attrib count_gender length = 3;
count_gender = 0;
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
group by sex) AS b
where a.name = b.name);
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.