Category: Data management

Author resource: Joost Impink

Explanation

This script returns the (actual, non-adjusted) stock price on day='date'. In case 'date' is a non-trading day, the script returns the stock price of the previous day.

This macro comes in two flavors: dsfP and dsfP_local, where dsfP connects to WRDS and dsfP_local requires a local copy of crsp.dsf. (Having a local copy saves execution time; queries on CRSP datasets on WRDS sometimes take a long time to execute.)

To create a local copy of crsp.dsf, first determine which period you need data on and the variables you may need. The 'full' crsp.dsf data set is roughly 13 gigabyte.

Script to download a local copy (this is not the macro)

	libname crsp '...some local directory...';

	%let wrds = wrds.wharton.upenn.edu 4016;options comamid = TCP remote=WRDS;signon username=_prompt_;rsubmit;
	libname crspWrds '/wrds/crsp/sasdata/sd';

	PROC SQL;               
	  create table k as
	  select a.permno, a.date, a.prc, a.ret 
	  from crspWrds.dsf a
	  where year(date) >=2002;
		
	quit;

	proc download data=k out=crsp.dsf;run;

	PROC SQL;               
	  create table k as
	  select a.permno, a.date, a.ret , a.decret
	  from crspWrds.erdport1 a
	  where year(date) >=2002;
		
	quit;

	proc download data=k out=crsp.erdport1;run;

	endrsubmit;

By the way, for daily stock returns, you may use crsp.dsf or crsp.erdport1. Erdport1 also includes the stock return for the size decile (decret), so for computing size adjusted returns, using erdport1 may be sufficient. Note that not all firms that are on dsf are also on erdport1 (for 2009, there are 7084 unique permnos on dsf and 5900 unique permnos on erdport1).

Macro

/*
	macro: %dsfP
	This script returns the (actual, non-adjusted) stock price on day='date'.
	author: Joost Impink, Feb 2011
	
	variables required:
	 dset: source dataset (holding key, permno and date)
	 outp: output dataset (appends variables 'prc' and 'date', make sure they do not exist on source)
	 key: key that is unique for each observation (for example permno concatenated with a date or counter)
	 permno: name of variable that holds permno
	 date: name of variable that holds the date (for example the event date minus 2)

	In case 'date' is a non-trading day, the script returns the stock price of the previous day.
	This is why the script retrieves the stock price of 4 days relative to 'date' (days -3 till day 0). It returns
	the last available stock price (day -1 if 0 is missing, day -2 if day -1 and 0 are missing, etc).

*/

%macro dsfP(dset, outp, key, permno, date);

data work.dsfP_temp1 (keep = key permno date1 date2);
set  &dset;
permno = &permno;
key = &key;
date1 = &date - 4;
date2 = &date ;
run;

proc sort data = work.dsfP_temp1 nodup; by key permno date1 ;run;

* PRC is actual price, not adjusted for stock splits;

%let wrds = wrds.wharton.upenn.edu 4016;options comamid = TCP remote=WRDS;signon username=_prompt_;rsubmit;
libname crsp '/wrds/crsp/sasdata/sd';

proc upload data=work.dsfP_temp1 out=j;run;

PROC SQL;               
  create table k as
  select j.*, dsf.date, dsf.prc
  from crsp.dsf, j
  where dsf.permno = j.permno
    and (j.date1 <= dsf.date <= j.date2);
quit;

proc sort data=k nodup; by key permno date1 date;

proc download data=k out=work.dsfP_temp2;run;

endrsubmit;

* take the last observation ;

data work.dsfP_temp2 ;
set work.dsfP_temp2;
by key;
if last.key then output;
run;

proc sql;

	create table &outp as
	select a.*, b.prc, b.date
	from &dset a
	LEFT JOIN
		work.dsfP_temp2 b
	ON	a.&key = b.key;

quit;

* clean up;
proc datasets library=work;
   delete dsfP_temp1 - dsfP_temp2;
run;

%mend dsfP;

*************************************************************************************;


%macro dsfP_local(dset, outp, key, permno, date);

* See macro dsfP;

data work.dsfP_temp1 (keep = key permno date1 date2);
set  &dset;
permno = &permno;
key = &key;
date1 = &date - 4;
date2 = &date ;
run;

proc sort data = work.dsfP_temp1 nodup; by key permno date1 ;run;

* PRC is actual price, not adjusted for stock splits;

PROC SQL;               
  create table work.dsfP_temp2 as
  select a.*, b.date, b.prc
  from work.dsfP_temp1 a, crsp.dsf b
  where a.permno = b.permno
    and (a.date1 <= b.date <= a.date2);
quit;

proc sort data=work.dsfP_temp2 nodup; by key permno date1 date;

* take the last observation ;

data work.dsfP_temp2 ;
set work.dsfP_temp2;
by key;
if last.key then output;
run;

proc sql;

	create table &outp as
	select a.*, b.prc, b.date
	from &dset a
	LEFT JOIN
		work.dsfP_temp2 b
	ON	a.&key = b.key;

quit;

* clean up;
proc datasets library=work;
  * delete dsfP_temp1 - dsfP_temp2;
run;

%mend dsfP_local;

Sample usage:

%dsfP_local(dset=at.z1, outp=at.z2, key=keyFirmQtr, permno=permno, date=rdq-2);

%dsfP(dset=at.z1, outp=at.z3, key=keyFirmQtr, permno=permno, date=rdq-2);

‘key’ is a variable to uniquely identify the observations on the level of analysis. For example, it can be cusip and datadate combined, or permno and a date. (Permno, cusip etc are not suitable for this purpose when a single firm can enter the sample more than once).

 

Other Data management
SAS Winsorize macro

forum

Latest forum posts
Fama French 49 YES 49 !!! by CA Miller
date format by Emily
Discretionary Accruals by jwhi121
question about discretionary accrual models by kerrida
Combining Global and North American data by CA Miller
File for Eventus or SAS Event Study by samme
quarterly accruals by lxt88
Financial Statement Comparability - De Franco, Kothari and Verdi (2011, JAR) Replication by rowing
question about discretionary accrual models by Zenghui
problem in perl by KZ

All rights reserved. © 2010-2014 wrds.us [Copyright] [Privacy Statement] [Disclaimer] [About]