BookmarkSubscribeRSS Feed
Obsidian | Level 7

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');
dcl hiter i('h');

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);

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 =;
h.remove(key : new_sedol, key : pd);

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

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





KS -, 



Jade | Level 19

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(), calls. I suggest that you insert another such pair

rc = i.last();
rc =;

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.

Super User Tom
Super User

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 then call missing(last_A);
  if inA then last_A=coalesce(A,last_A);
  retain last_A;
  if inB;
  drop A;

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.

Jade | Level 19

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;

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

  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;

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.


The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4 in conversation