I am trying to calculate different duration periods that measure how long a firm remains in a given manager's portfolio. The data structure looks as follows: There are distinct groups of two managers (where one of the managers may appear several times in the data in different groups). Within the groups, each manager holds several companies (some firms may be hold by both managers, but these cases are very rare). Whenever a manger holds a given company in his/her portfolio, then the manger has to disclose its share rate (reporting date is given). NOTE: It is possible that there may be gaps between the reporting dates for a given group-manager-firm pair. Why does this happen? Occasionally, the manager buys shares of a given company, sell these shares, and then buys new shares after certain time has passed (i.e., this may happen with gaps). However, whenever rate >=0.01, the manager has to disclose the holding. What have I done? I have calculated how long (i.e., many quarters ) a group-manager holds a given company. (Holding_Duration1), but have ignored any gaps (biasing this variable). I have calculated how long (i.e., many quarters ) a group-manager holds a given company in which the share rate is above 5%. (Holding_Duration2), but have ignored any gaps (biasing this variable). What am I missing? (I want to overcome the bias of not taking into account for the gaps between reporting dates) How many quarters has it passed since the first reporting date until the event date? (Event_minus_First) How many quartesr has it passed since the very last reporting data after which there were no reporting date gaps? (Last_Subsequent_Duration). NOTE: The reason of calculating this variable is to avoid the following problem. Assume in 2001Q1, a given manager purchased shares in firm ABC. Assume the manager hold his/her shares above 5% until 2003Q2, but then sold off all shares sometime between 2003Q2 and 2003Q3 (so that particular firm disappears in 2003Q2). Then in 2005Q1, the manager again purchased shares from firm ABC and hold his/her shares above 5% until the event date which was in 2008Q1. In this case, Last_Subsequent_Duration=12 because 12 quarters has passed since the most recent reporting date after which the manager did not sell his / her shares of company ABC. Here are my "have" as well as "want" datasets: DATA have1; INPUT Mgrno Firm_ID $ Report_Date Date9. Rate; FORMAT Report_Date Date9.; DATALINES; 19500 12727AZ 31MAR1980 0.04 19500 12727AZ 30JUN1980 0.05 19500 12727AZ 30SEP1980 0.08 19500 12727AZ 31DEC1980 0.07 19500 12727AZ 31MAR1981 0.09 19500 12727AZ 30JUN1981 0.02 19500 12727AZ 30SEP1981 0.08 19500 12727AZ 31DEC1981 0.05 19500 12727AZ 31MAR1982 0.07 19500 12727AZ 30JUN1982 0.10 39547 12727AZ 30JUN1980 0.02 39547 12727AZ 30SEP1980 0.01 39547 12727AZ 31DEC1980 0.06 39547 12727AZ 31MAR1981 0.06 99999 731238A 31MAR1982 0.11 99999 731238A 30JUN1982 0.12 99999 731238A 30JUN1983 0.07 ; run;
Data have2;
INPUT Mgrno Firm_ID $ Event_Date Date9. Rate Group $;
FORMAT Event_Date Date9.;
DATALINES;
19500 12727AZ 31MAR1982 0.07 AA
39547 12727AZ 31MAR1981 0.06 BB
99999 731238A 30JUN1983 0.07 BB
;
run;
/*
Holding_Duration1=Number of quarters past until the event_date since the company was included in the portfolio of the manager for the first time until the event_date
Holding_Duration1=Number of quarters past until the event_date since the company was included in the portfolio of the manager for the first time until the event_date
as well as where the rate >= 0.05
Total=Number of quarter past from the first time the company was added into the porfolio until the event_date
Event_minus_First= Event date - First date in terms of number of quarters */
Data want;
INPUT Mgrno Firm_ID $ Event_Date Date9. Rate Group $ Holding_Duration1 Holding_Duration2 Total Event_minus_First Last_Subsequent_Duration;
FORMAT Event_Date Date9.;
DATALINES;
19500 12727AZ 31MAR1981 0.07 AA 8 6 8 8 6
39547 12727AZ 31MAR1981 0.06 BB 4 2 4 4 6
99999 731238A 30JUN1983 0.07 BB 3 3 3 7 1
;
run; Here is my code for the "what I have done" part: *First I will include all the reported holding filings of the manager for the given firm;
proc sql;
create table first_method_V1 as
select *
from have1 as a
right join have2 as b
on (a.mgrno=b.mgrno) & (a.firm_id=b.firm_id);
quit;
proc summary data=work.first_method_V1 nway;
var Report_Date;
class mgrno group;
output out=work.Holding_Duration1
n=Holding_Duration1;
run;
data only_5percent;
set first_method_V1;
if rate >= 0.05 & Event_Date >= Report_Date;
run;
proc summary data=work.only_5percent nway;
var Report_Date;
class mgrno group;
output out=work.Holding_Duration2
n=Holding_Duration2;
run;
data first_method_V2(drop= _TYPE_ _FREQ_);
merge first_method_V1 Holding_Duration1 Holding_Duration2;
by mgrno group;
run; Thanks in advance for any suggestions / help you may provide.
... View more
