Finding Analysts Forecast Revision
Posted: 29 July 2015 10:25 PM   [ Ignore ]
Newbie
Rank
Total Posts:  4
Joined  2015-07-29

Dear Joost,

I am using IBES to find analysts’ forecast revision for my research and I am using SAS 9.4 for cleaning up my data. My analysts’ forecast revision is measured by =  ((forecast at time t - median forecast at time t-1quarter)/forecast at time t-1quarter). However I am facing issues with lagging the previous forecast at time t-1quarter.

My main story is to achieve: if fpedats (aka forecasting date) is a quarter (3 months) or less than the previous fpedats, then i would lag the median_forecast of previous quarter t onto VALUELAG variable so that I can calculate the value of analysts forecast revision. This VALUELAG variable is conditioned on cusip (specific firm) as well. If fpedats at time t is more than a quarter (3 months), then VALUELAG will be empty.
HOWEVER: How can I hardcode or smartcode it to apply the conditions to all my VALUELAG row variables? If you see the excel file, what I have is that some VALUELAG values are empty for all the subsequent variables?


below are my coding:
/*To establish BDATE and EDATE to prepare to lag median_forecast - which i calculate previously*/  /* I have found the median EPS forecast in previous coding*/

data Thesis.forecastquarterly_median_mean;
set Thesis.forecastquarterly_median_mean;
BDATE=INTNX(‘MONTH’,fpedats,-3,‘B’);
EDATE=INTNX(‘MONTH’,fpedats,0,‘E’);
FORMAT BDATE DATE9.;
FORMAT EDATE DATE9.;
RUN;

/* create forecast_sum1 with important variables to find Analysts forecast revision*/
PROC SQL;
CREATE TABLE forecast_sum1 AS SELECT A.cusip, A.cname, A.obsno, A.analys, A.fpi, A.value, A.announce_dttm, A.actvtn_dttm, A.review_dttm, A.fpedats,A.actual,A.BDATE, A.EDATE, B.*
FROM Thesis.forecastquarterly_median_mean AS A INNER JOIN
work.sample AS B
ON A.cusip=B.cusip AND A.EDATE = B.fpedats AND A.announce_dttm < B.fpedats
order BY cusip,fpedats;

QUIT;

/*to remove extra duplicates*/
proc sort data=work.forecast_sum1 nodupkey;by cusip fpedats;
run;


/* Conditioning ValueLag (aka. median_forecast) based one BDATE < fpedats (aka forecast end date) less than 30 days and condition on cusip (firm’s code number) */
data work.forecast_sum1;
set work.forecast_sum1;
by CUSIP fpedats;
VALUELAG = ifn(BDATE - lag(fpedats) <=30 and EDATE NE lag(fpedats) and cusip EQ lag(cusip), lag(median_value),.); /* if conditions are met, then print lagged median value*/
run;


THANK YOU JOOST! smile

File Attachments
for_Forum_Wrds_Analysts_forecast_revision.xlsx  (File Size: 14KB - Downloads: 202)
Profile
 
 
Posted: 30 July 2015 06:18 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Nochtan,

Instead of the data step with the lag (last step), I would do another join (keep the first as is).

I don’t understand how your dataset has individual analyst forecasts and at the same time has medians, but I am assuming you have cooked up that dataset yourself.

The main thing (to get the 3 month before fpedats) could be done with something like:

PROC SQL;
CREATE TABLE forecast_sum2 AS SELECT ... (variables you want)
FROM forecast_sum1  AS A left join Thesis.forecastquarterly_median_mean AS B
on A
.cusip=B.cusip 
/* need earlier quarter, that is 3 months earlier in time */
having intck("month"b.fpedatsa.fpedatseq 3;
QUIT

Table a is the table you have, I am matching it again with Thesis.forecastquarterly_median_mean which seems to hold the IBES data. You probably need some more conditions (on announcement date), but the key thing is the use of intck: ‘having intck(“month”, b.fpedats, a.fpedats) eq 3’ will be true if the previous period is 3 months earlier

INTCK function: http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p1md4mx2crzfaqn14va8kt7qvfhr.htm

Using a left join will keep the observations that have no earlier quarter in the created dataset (will have missing values for the variables in B).

Hope this helps,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 01 August 2015 12:26 AM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  4
Joined  2015-07-29

Hi Joost!

That was really helpful Thanks! And also yes, the median value is created by myself using PROC MEANS.

I found out that we cant use lagged variable for this instance, because the result of median_value lagged variable would only print on the first row.

I heed your advice and created a separate table, below are my codes for others to reference:

data Thesis.forecastquarterly_median_mean;
set Thesis.forecastquarterly_median_mean;
BDATE=INTNX(‘MONTH’,fpedats,-3,‘B’); /*BASE DATE To be 3 months before End Date*/
EDATE=INTNX(‘MONTH’,fpedats,0,‘E’);
FORMAT BDATE MMYYn9.;
FORMAT EDATE MMYYn6.;
BDATENEW = putn(BDATE, ‘MMYYn.’); /*Because we can merge data with dates, we create Base Date New so that forecast_median values can be merged with main data via numerics (instead of date)*/
EDATENEW = putn(EDATE, ‘MMYYn.’); /* create EDATENEW for numeric merger (similar to BDATENEW)*/
RUN;

proc sql;
create table test1 as
select obsno as obs, EDATENEW as laggeddate, median_value as laggedvalue from work.forecast_sum1;
quit;

/* 7. Merging Forecast_median values with Main data - condition on Observation number and EDATENEW = BDATENEW*/
proc sql;
create table Thesis.test2 as
select b.cusip, b.cname,b.obsno,b.analys,b.value,b.fpedats,b.actual,b.BDATENEW,b.EDATENEW,a.laggedvalue as Median_Value,a.laggeddate,b.VALUELAG,b.cond1,b.cond2,b.cond3 from
forecast_sum1 as b, test1 as a where a.obs = b.obsno and a.laggeddate = b.BDATENEW;
quit;

proc sql;
create table Thesis.test2 as
select * from forecast_sum1 left join test1 on test1.laggeddate = forecast_sum1.BDATE and test1.obs = forecast_sum1.obsno;
quit;

proc sort data = Thesis.test2 nodupkey out = Thesis.test2 dupout= work.test2excluded;
by cusip FPEDATS ANALYS ;
run;


I did use your SAS intck formula and it could fit nicely too. But my formula above would work too, but it is just messier compared to yours.

Cheers Joost!

Profile
 
 
Posted: 01 August 2015 06:18 AM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Nochtan,

Good! Thanks for following up!

Cheers,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile