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!