Category: Data management

Author resource: Joost Impink

Explanation

This macro downloads WRDS datasets to the local pc and creates an index file (if index file is available on WRDS). Instead of using remote submit, the datasets can be accessed from the local hard drive. Creating index files should increase the performance for large datasets.

The macro uses Clay's array macros (documentation).

Note: this macro has not been extensively tested, so please proceed with caution.

/*
	Macro: 			dlDataset.sas
	Created by:		Joost Impink
	Date: 			May 2012
	
	Purpose:		Copy WRDS datasets to local computer, and create (local) index files 
	
	Variables:
	- wrdsLibDir		library directory on WRDS
	- wrdsDs			dataset to download from WRDS
	- localLibDir		local library directory (must be empty when creating index file)
	- createIndexFile	indicator, if 1 then index file is copied from WRDS (if available)
	
	Dependencies:
	This macro uses the %runquit macro (see below) and Clay's array functions (link above). 
	These macros are included from this website. It is recommended to download these and include
	them locally from the hard drive. 
	
	Note: The purpose of this macro is to download often-used datasets so they can be 
	used locally. If WRDS has an index on the dataset, the same indices are created 
	locally (i.e.,if createIndexFile is set to 1).
	The script will only work correctly if no index previously exists. So, the script 
	will work well when downloading datasets to an empty folder.
	If createIndexFile is set to 1 and no index exists, the script will run without 
	errors, but no index file will be created.

*/

/*	Include macro files used (%ARRAY, %DO_OVER, %runquit) 
	It is recommended to download these files, and include like this:
	%include "P:\projects\macros\array_functions.sas";
	%include "P:\projects\macros\run_quit.sas";
*/

/*	Include array function macros */

filename m1 url 'http://www.wrds.us/macros/array_functions.sas';
%include m1;

/*	Include runquit macro */

filename m2 url 'http://www.wrds.us/macros/runquit.sas';
%include m2;

%macro dlDataset(wrdsLibDir=, wrdsDs=, localLibDir=, createIndexFile=0);

libname dlLocal &localLibDir;

%let wrds = wrds.wharton.upenn.edu 4016;options comamid = TCP remote=WRDS;
signon username=_prompt_;

/*  Push the macro variables to the remote server */ 

%syslput wrdsLibDir			=&wrdsLibDir;
%syslput wrdsDs				=&wrdsDs;
%syslput localLibDir		=&localLibDir;
%syslput createIndexFile	=&createIndexFile;

rsubmit;

libname dl &wrdsLibDir;
 
/*	Download dataset */

proc download data=dl.&wrdsDs out=dlLocal.&wrdsDs;run;

/*	If index file needs to be created */

%if &createIndexFile = 1 %then %do;

		/*	Delete old indices file (important, SAS will keep old file if 
		no index file exists in following proc datasets) */

		proc datasets library=work; delete out2; run;

		proc datasets lib=dl; contents data=&wrdsDs out2=work.out2; quit; 

		/*	If no indices exists, an empty file will be downloaded; this 
			will not cause errors (in that case, no indices will be created
			of course) */

		proc download data=out2 out=DsIndices;run;

%end;
endrsubmit;

/*	If index file needs to be created */

%if &createIndexFile = 1 %then %do;

	/*	Push recreate statements into array */

	%ARRAY(indices, data=DsIndices, var=Recreate);

	/*	Create indices by looping over array with recreate command 
		This only works if the index does not yet exist (works for 
		newly created dataset, and not if already an index file exists)

		In case the index file (DsIndices) does not exist, the array 
		"indices" will be empty and no indices will be created 
		(modify will be reported as succesful)
	*/

	proc datasets library=dlLocal ;
		modify &wrdsDs;
		%DO_OVER(indices);
	%runquit;

	  /*  Clean up */        
	proc datasets library=work;     
		delete DsIndices;
	%runquit;

%end;

%mend;

Usage

Sample usage:

/*	Crsp (annual update) dsenames, dsf, msf 
	Note that dsf is a large dataset (more than 12 gigabyte);
	downloading as well as creating the indices for dsf may 
	take several hours
*/

%let remoteCrspDir 	= "/wrds/crsp/sasdata/a_stock";
%let localDir  		= "C:\temp\temp_download\crsp";

%dlDataset(wrdsLibDir=&remoteCrspDir, wrdsDs=dsenames, 	localLibDir=&localDir, createIndexFile=1);
%dlDataset(wrdsLibDir=&remoteCrspDir, wrdsDs=dsf, 		localLibDir=&localDir, createIndexFile=1);
%dlDataset(wrdsLibDir=&remoteCrspDir, wrdsDs=msf, 		localLibDir=&localDir, createIndexFile=1);

/*	Crsp (annual update) market indices */

%let remoteCrspDir 	= "/wrds/crsp/sasdata/a_indexes";

%dlDataset(wrdsLibDir=&remoteCrspDir, wrdsDs=dsix, 		localLibDir=&localDir, createIndexFile=0);
%dlDataset(wrdsLibDir=&remoteCrspDir, wrdsDs=msix, 		localLibDir=&localDir, createIndexFile=0);

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]