BookmarkSubscribeRSS Feed
asishgautam
Calcite | Level 5

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

7 REPLIES 7
ballardw
Super User

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.

asishgautam
Calcite | Level 5

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.

asishgautam
Calcite | Level 5

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 ;

FloydNevseta
Pyrite | Level 9

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;

Ksharp
Super User

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

asishgautam
Calcite | Level 5

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

art297
Opal | Level 21

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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 3291 views
  • 0 likes
  • 5 in conversation