DATA Step, Macro, Functions and more

understanding sql error

Reply
Contributor
Posts: 66

understanding sql error

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

Super User
Posts: 11,343

Re: understanding sql error

Posted in reply to asishgautam

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.

Contributor
Posts: 66

Re: understanding sql error

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.

Contributor
Posts: 66

Re: understanding sql error

Posted in reply to asishgautam

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 ;

Frequent Contributor
Posts: 101

Re: understanding sql error

Posted in reply to asishgautam

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;

Super User
Posts: 10,044

Re: understanding sql error

Posted in reply to asishgautam

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

Contributor
Posts: 66

Re: understanding sql error

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

PROC Star
Posts: 7,492

Re: understanding sql error

Posted in reply to asishgautam

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 ;

Ask a Question
Discussion stats
  • 7 replies
  • 1087 views
  • 0 likes
  • 5 in conversation