topic Re: understanding sql error in SAS Programming

understanding sql error

asishgautam — Wed, 01 Aug 2012 20:42:38 GMT

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

Re: understanding sql error

ballardw — Wed, 01 Aug 2012 22:28:47 GMT

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.

Re: understanding sql error

asishgautam — Wed, 01 Aug 2012 23:59:24 GMT

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.

Re: understanding sql error

asishgautam — Thu, 02 Aug 2012 00:07:06 GMT

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 ;

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 ;

Re: understanding sql error

Ksharp — Thu, 02 Aug 2012 07:16:50 GMT

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

Re: understanding sql error

asishgautam — Thu, 02 Aug 2012 15:09:58 GMT

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

Re: understanding sql error

art297 — Thu, 02 Aug 2012 15:35:09 GMT

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 ;

Re: understanding sql error

FloydNevseta — Thu, 02 Aug 2012 16:58:21 GMT

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;