How to resolve the error " Cannot remove record from a hash object which has been locked by an iter"

[KS99]

Hi, greetings, SAS family! 

I lead my academic life every day in indebtedness to the SAS community. I have been helped so many times when I was in great need. 

I want to ask you this question today. 

Let's say we have a dataset (Forc_sample) of firm-dates based on earnings announcements. On the other hand, we have another dataset (Mna_sample) of firm-dates based on M&A announcements. Earnings announcements are much more frequent (quarterly) than M&A announcements. Two announcements rarely occur on the same date. Given such two kinds of dates for a firm, I want to merge the row of M&A announcement date with the row of the nearest date of earnings announcement following M&A announcement date. 

In fact, I am using the following SAS codes. I obtained them from the SAS community: 

data WANT(drop = c d rc);
if _N_ = 1 then do;
dcl hash h(dataset:'Mna_sample(rename = (new_sedol = c ANNDATS_ACT = d)', ordered : 'Y');
h.definekey('c', 'd');
h.definedone();
dcl hiter i('h');
end;

set Forc_sample;
c = '        ';
d = .;
if h.check(key : new_sedol, key : ANNDATS_ACT) = 0 
then do;pd = ANNDATS_ACT;
h.remove(key : new_sedol, key : ANNDATS_ACT);
end; 

else do;h.add(key : new_sedol, key : ANNDATS_ACT, data : new_sedol, data : ANNDATS_ACT);
if i.setcur(key : new_sedol, key : ANNDATS_ACT) = 0 
then if i.prev() = 0 & new_sedol = c & ANNDATS_ACT - d <= 180 then do;pd = d;
rc = i.last();
rc = i.next();
h.remove(key : new_sedol, key : pd);
end;

h.remove(key : new_sedol, key : ANNDATS_ACT);
end;

event = pd > .;
 format pd mmddyy10.;run;

This code works perfectly. The problem is that, when I run a series of such tasks, SAS gives an error message like the following. 

"ERROR: Cannot remove record from a hash object which has been locked by an iterator at line 16 column 1" 

I tried everything that I can do hardware-ily, such as turning off the computer and rerunning that portion of codes, etc. But of no use. 

I also searched about in in the internet and obtained a remedy of "h.delete()" something. But I don't know how to use it in my codes. Please help!! 

Thank you in advance!! 

Sincerely, 

KS -, 

[FreelanceReinh]

Hi @KS99,

---

@KS99 wrote:

"ERROR: Cannot remove record from a hash object which has been locked by an iterator at line 16 column 1" 

---

This error message means that you try to remove a hash object item that the hash iterator i is currently pointing at. There are three calls of the REMOVE method in your code. I suspect that the conflicting one is the first. The second call is safe because of the preceding i.last(), i.next() calls. I suggest that you insert another such pair

rc = i.last();
rc = i.next();

before the third (!) h.remove(...). This should help even if that third h.remove(...) causes the conflict and it is in the DO-END block where the iterator methods are used. Of course, please check the results after this change.

[Tom]

Good luck with getting the hash approach to work.

For matching data to the most recent (or if sorted with DESCENDING the closest following) event is easily done by INTERLEAVING the observations.

Not clear what your data is so let's just make an example.  Say you have dataset A with variable ID, DATE and A.  And second dataset B that also has ID and DATE.  So you want to add to B the last value of A that appears ON or BEFORE.  Then just do something like this:

data want;
  set a(in=inA keep=id date a) b(in=inb);
  by id date;
  if first.id then call missing(last_A);
  if inA then last_A=coalesce(A,last_A);
  retain last_A;
  if inB;
  drop A;
run;

If you reverse the order of A and B in the SET statement then values of A that occur on the same day will not count.

If you sort by ID DESCENDING DATE then you can find the value of A that occurs afterwords.

[mkeintz]

My understanding is that

1. You want one output record for each M&A record  (dataset M_and_A in the code below).
2. For each M&A record you want an earnings record posted on the same date, or the earliest following date (from dataset EARNINGS).

If both of your datasets are sorted by ID (e.g. ticker, cusip, permco), and DATE, then you can take advantage of conditional SET statements (if some_condition then SET some_dataset), to carry forward (or in this case, carry backward) data of interest.

You might think you have to sort data in reverse chronological order within each id, then use conditional set's to logically carry forward (but in real calendar terms, carry backward) the needed matching data.  Having done that, you'd have to re-sort back to chronological order.

You don't have to do that.

Instead, just recognize that carrying back a given earnings record dated, say 02JUL2023, back to (but not including) the previous earnings record (say 31mar2023), is the same as carrying forward the 02JUL2023 data from 01APR2023 (day after preceding earnings) forward through 02JUL2023.  An inexpensive (compared to sorting and re-sorting) dataset view can enable this approach.  In other words, carrying data from date D{t} backwards is the same as carrying forward the same data from date D{t-1}+1, so just create a dataset view using this concept.

Something like this:

data earnings_next/ view=earnings_next;
  set earnings;
  by id;
  _sort_date=ifn(first.id,'01jan1960'd,sum(lag(date),1));
run;

data want 
  (label="Every M&A record plus either the sameday earnings or earliest following Earnings"
   drop=_:);

  merge m_and_a       (in=in_m_and_a  rename=(date=_sort_date) keep=id _sort_date)
        earnings_next (in=in_earnings keep=id _sort_date);
  by id _sort_date;

  if in_earnings then set earnings_next (rename=(date=earnings_date));
  if inm_and_a then set m_and_a (rename=(date=M_and_A_date)) ;
  if inm_and_a;
run;

The first MERGE is unconditional, which means all the variables it retrieves from the merged datasets are replaced (or set to missing) with every new distinct value of ID/_SORTDATE.  I.E. all earnings data are set to missing every time an M&A record is read (without an exact earnings match), and vice-versa.  But this unconditional MERGE reads only the sorting variables ID and _SORT_DATE, to avoid overwriting data from the subsequent conditional SETs.  

The conditional set statements later in the code means that the relevant data is NOT replaced or set to missing until the condition is met.  So earnings data records are logically carried forward (but actually carried backward using the modified _SORT_DATE in earnings_next) and held.  It is still there when a subsequent M&A record is encountered, (inm_and_a=1), M&A data is read, and the merged data is output.

I have a 2017 paper on this:  History Carried Forward, Future Carried Back: Mixing Time Series of Differing Frequencies 

A couple notes on the code: 

1. In making the earnings_next view, I use 01jan1960 as the default _SORT_DATE value for the first record of each ID.  You can use whatever date value you want (including a missing value), as long as it precedes the earliest actual DATE value in both datasets.
2. The resulting data will have one merged record for each M_and_A record, with two DATE variables M_and_A_date and earnings_date.