Hi,
I am working on the fama french paper and want to estimate the pre ranking betas. These betas are based on returns that each securities had 5 years ago. I used the following code in an attempt to achieve this;
data first;
set size1(keep=permno date);
by permno;
retain firstdate;
date=intnx('month', date, 1)-1;
if first.permno then firstdate=date;
if last.permno then do;
lastdate=date;
output;
end;
run;
data firstandlastdates;
set first;
format firstdate yymmdd10.;
format lastdate yymmdd10.;
run;
data permnosrankdates(rename=(date=rankdate));
set firstandlastdates;
date=firstdate;
do while(date<=lastdate);
output;
date=intnx('month', date+1, 1)-1;
end;
run;
data permnosrankdates;
set permnosrankdates;
date=rankdate;
i=1;
do while(i<=60);
output;
date=intnx ('month', date, 0)-1;
i=i+1;
end;
run;
data permnosrankdates;
set permnosrankdates;
format date yymmdd10.;
run;
data ff;
set size1 (keep=date ret vwretd exchcd);
date=intnx('month', date, 1)-1;
run;
proc sort data=permnosrankdates;
by date permno;
run;
proc sort data=ff;
by date;
run;
data permnosrankdates;
merge permnosrankdates(in=a) ff(in=b);
by date;
if a and b;
run;
data msf;
set size1(keep=permno date ret vwretd exchcd);
where ret is not missing;
date=intnx('month', date, 1)-1;
run;
proc sort data=msf;
by date permno;
run;
data permnosrankdates;
merge permnosrankdates(in=a) msf(in=b);
by date permno;
if a and b;
run;
Some output was obtained, however i got some very high beta values (for example -1500) when i ran the regressions later which suggests that something went wrong. Could anyone please help me to identify mistake in this code? Did this code lag the values correctly?
In addition, I also tried to lag the data by 60 months by using the regular lag function. This also doesn't work as the data I have is a cross section (panel data) of returns and stocks for the time period (1980-2015). Converting the date to yyyymm format and then lagging by 60 months doesn't help either.
So my question is: How can I obtain the 60 months lagged returns of securites for a cross section of stock returns that is identified by and id variable=permno and a time varieable date? Could anyone please write the correct sas code for this problem? The data I am using is the cross section of stock returns for given dates.
I would be greatful for any reply and help. I look forward for a reply.
Regards
Akarsh
PS: i am using sas version 9.4
Ordinarily you would get no answers to this question becuase you did not provide sample data for SIZE1, for FF, and for your desired outcome.
But I believe I am familiar with the particular data sources you are using.
You have data in SIZE1 sorted by permno/date in dataset size1, right? I guess it's CRSP monthly data, where the date is the last trading day of the month. You want to merge it with FF data, which I believe is also monthly, but is only a single series (i.e. no permno) and apparently uses last calendar date of the month. If that's the case, then it's best to load the FF into a lookup table (called a hash object below) and just retrieve the needed values by means of hash lookup. No need for all your sorting.
data need;
set size1;
if _n_=1 then do;
if 0 then set FF;
declare hash ffdata(dataset:'ff');
ff.definekey('date');
ff.definedata(all:'Y');
ff.definedone();
end;
date=intnx('month',date,0,'end');
rc=ffdata.find();
date60=intnx('month',date,-60,'end');
run;
Notes:
This assumes, of course, that the FF has all the date values found in SIZE1 (after end-of-month adjustment). Otherwise some FF values will be erroneous in the progarm above. You would need to explicitly set them to missing whenever RC^=0. So take a look at NEED before you run the program below to generate WANT.
Now you want 60 month lagged returns. For record J of a given permno you can just find record K of the same permno where DATE60 for J = DATE for K. That's straightforward:
data want;
merge need (in=incurrent
need (in=in60months keep=date ret vwretd rename=(date=date60 ret=ret60 vwretd=vwretd60));
by permno date60;
if incurrent and in60months;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.