Editte note: you still haven't provided a working data step, so the code below is untested.
This looks like Execucomp data from Compustat. If so, then fiscal year (variable FYEAR) need not include January 1st of the same nominal year. For instance, fiscal year May 1993-April 1994 is identified as fiscal year 1993 (more months in calendar 1993 than calendar 1994), but does not include Jan 1, 1993. If a person became CEO of such a company in February 1994 you would presumably miss the fact that they overlap with FYEAR=1993. Isn't there another variable indicating which month (with possible values 1 through 12) is the last of a fiscal year Fiscal years ending in January - through May are identified with the year in which they start. Fiscal years ending in June through December are identified with the year containing the end month.
And more generally, you haven't stated what you want done when two people are CEO's in the same FYEAR. You show one record per FYEAR. So if there are two (or more!) CEO's in one fiscal year, what do you want in the result? Multiple records? One record with the CEO having has the most months? But if so, then how will you determine which CEO has the most months? Especially given that fiscal years need not align with calendar years.
Also, presumably you have this need to retrieve future values for multiple companies (multiple gvkeys?), so you would need your sample data to have gvkey, and to be sorted by gvkey fyear.
data want (drop=y);
set have (in=firstpass) have (in=secondpass);
by gvkey ;
array id_hist {1990:2020} _temporary_;
if first.gvkey then call missing(of id_hist{*});
if firstpass=1 and nmiss(begindate,enddate)=0 then do y=year(begindate) to year(enddate);
id_hist(y)=execid;
end;
if secondpass;
if missing(execid) then execid=id_hist{fyear};
run;
For any observation with a blank execid, if that fyear value overlapped if more than one date range for CEO's, this program would return the latter execid.
Also note this program does not sort in descending chronological order, then carry "forward" values, then re-sort in ascending order. Instead it reads each GVKEY twice - once to establish an ID_HIST, the second time to retrieve from that history.
Finally, this program covers 1990 through 2020. If you need more years, change the lower and/or upper bounds of the id_hist array.
... View more
