I am trying to understand why I am getting this error. I suspect it has to do with the self-referencing but I am not sure - my thinking was that the outer subquery was an "independent" action:
proc sql noprint ;
update Hourly_p
set Holiday_id = 1
where Date_val in (select intnx('day',Date_val,1)
from Hourly_p
where Holiday_id = 1 and weekday(Date_val) = 1
)
;
quit ;
the error message I received is:
WARNING: A value expression of the SET clause references the data set being updated.
ERROR: You cannot reopen WORK.HOURLY_P.DATA for update access with member-level control because
WORK.HOURLY_P.DATA is in use by you in resource environment SQL.
ERROR: PROC SQL could not undo this statement if an ERROR were to happen as it could not obtain
exclusive access to the data set. This statement will not execute as the SQL option
UNDO_POLICY=REQUIRED is in effect.
237 quit ;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
However, if I split the update into two steps - things work just fine:
proc sql noprint ;
create table test1a as
select intnx('day',Date_val,1) as Key1
from Hourly_p
where Holiday_id = 1 and weekday(Date_val) = 1 ;
update Hourly_p
set Holiday_id = 1
where Date_val in (select Key1
from test1a
)
;
quit ;
238 proc sql noprint ;
239 create table test1a as
240 select intnx('day',Date_val,1) as Key1
241 from Hourly_p
242 where Holiday_id = 1 and weekday(Date_val) = 1 ;
NOTE: Table WORK.TEST1A created, with 24 rows and 1 columns.
243
244 update Hourly_p
245 set Holiday_id = 1
246 where Date_val in (select Key1
247 from test1a
248 )
249 ;
NOTE: 24 rows were updated in WORK.HOURLY_P.
250 quit ;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.12 seconds
cpu time 0.01 second
The error
ERROR: You cannot reopen WORK.HOURLY_P.DATA for update access with member-level control because
WORK.HOURLY_P.DATA is in use by you in resource environment SQL.
usually means that you have the dataset WORK.HOURLY_p open somewhere. Possibly you have opened the table using Explorer or the Viewtable command. Or you have the columns view open in Explorer.
The solution is to close whatever is using the dataset. In your case the nested reference to the same data set appears to be the offending procedure. Which is why the separation works, you only reference the set once.
Hi,
I ran this again and made sure Hourly_p was not open - I did not have it open. I am still getting the same error message.
this is my full code:
/* id tou periods for sce tou-gs-3 tariff */
data Hourly_p (drop = Dy rename = (i = Dy)) ;
format Date_val date9. ;
do Mnth = 1 to 12 ; /* # of months */
select ; /* get # of days/month */
when (Mnth = 2) Dy = 28 ;
when (Mnth in (4,6,9,11)) Dy = 30 ;
otherwise Dy = 31 ;
end ;
do i = 1 to Dy ;
Date_val = mdy(Mnth,i,2002) ;
do Time_id = 1 to 24 ; /* # of hours/day */
/* id holidays from tariff sheet */
select ;
when (Date_val in ('01JAN2002'd,'18FEB2002'd,'27MAY2002'd,'04JUL2002'd,'22DEC2002'd,
'11NOV2002'd,'02SEP2002'd,'28NOV2002'd,'25DEC2002'd
)
) Holiday_id = 1 ;
otherwise Holiday_id = 0 ;
end ;
/* id summer/winter */
select ;
when ('01JUN2002'd<=Date_val < '01OCT2002'd) Season_id = 'Summer' ;
otherwise Season_id = 'Winter' ;
end ;
output ;
end ;
end ;
end ;
run ;
/* now update any monday to holiday if holiday fell on sunday */
proc sql noprint ;
update Hourly_p
set Holiday_id = 1
where Date_val in ( select intnx('day',Date_val,1)
from Hourly_p
where Holiday_id = 1 and weekday(Date_val) = 1
)
;
quit ;
Try this. I simplified your original data step a bit and added the logic to determine if a Monday is also a holiday.
data hourly_p ( drop=_: );
_yr = 2002;
_prv_holiday_id = 0;
format date_val date9.;
do date_val = mdy( 1,1,_yr ) to mdy( 12,31,_yr );
mnth = month( date_val );
dy = day( date_val );
* determine holidays;
select;
when (Date_val in ('01JAN2002'd,'18FEB2002'd,'27MAY2002'd,'04JUL2002'd,'22DEC2002'd,
'11NOV2002'd,'02SEP2002'd,'28NOV2002'd,'25DEC2002'd
)
) Holiday_id = 1 ;
* the monday following a sunday holiday is also a holiday;
when ( _prv_holiday_id = 1 and weekday( date_val ) = 2 ) holiday_id = 1;
otherwise Holiday_id = 0 ;
end ;
/* id summer/winter */
select ;
when ('01JUN2002'd<=Date_val < '01OCT2002'd) Season_id = 'Summer' ;
otherwise Season_id = 'Winter' ;
end ;
do Time_id = 1 to 24 ; /* # of hours/day */
output ;
end ;
* the current holiday_id becomes the previous holiday_id for the next loop;
_prv_holiday_id = holiday_id;
end;
run;
Maybe the reason is you identify the same dataset twice at one sql statement.
proc sql noprint ;
update Hourly_p
set Holiday_id = 1
where Date_val in (select intnx('day',Date_val,1)
from Hourly_p
where Holiday_id = 1 and weekday(Date_val) = 1
)
;
quit ;
Ksharp
Thats what I had started to suspect. So it seems that breaking up the code into 2 steps is the only way to do the update...
Since there are a lot of duplicate dates being selected, a distinct statement would also help and you could use a macro variable rather than create an additional file. e.g.:
proc sql noprint ;
select distinct intnx('day',Date_val,1) into :holidays
separated by ","
from Hourly_p
where Holiday_id = 1 and weekday(Date_val) = 1
;
update Hourly_p
set Holiday_id = 1
where Date_val in (&holidays.)
;
quit ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.