This post is based on a presentation I was asked to give our second-year PhD students. The presentation focused on using the databases that the university subscribes to, and it includes two specific examples of the benefits of using a SAS connection rather than the online menus to pull data.
Wharton Research Data Services taps the most comprehensive sources of financial, accounting, economic, management, marketing, banking, and insurance data. Must use Internet Explorer Company financials, earning estimates, market data and stock quotes, Venture Capital/Private Equity data, press releases, transaction data, corporate filings, and more. May 08, 2020 Must use Internet Explorer Company financials, earning estimates, market data and stock quotes, Venture Capital/Private Equity data, press releases, transaction data, corporate filings, and more. The resulting database is then manually matched to DealScan by name, filling in any missing information via manual search on the internet, and accounting for mergers. At this stage, we can link bank level variables obtained from Call Reports to specific lenders. Aug 19, 2020 Historical information on public companies. Includes Industrial Manual 1920-, OTC Industrial Manual 1970-, OTC Unlisted Manual 1986-, Bank & Finance Manual 1928-, Public Utility Manual 1914-, Transportation Manual 1909-, International Manual 1981. Also includes Municipal & Government Manual.
This is an example of steps one could take if one were to search for the audit opinions of firms traded on equity exchanges outside of the United States.
First, I have created a folder named c:replicationpark directly on the C-drive of my computer. If you are going to use the .do files and .sas files included in this document, you must have the same folder on your c-drive. Do not create this within your documents or user folder! Go directly onto your C-drive to create this folder.
Dealscan Database Manual 2017
When using online menus for WRDS accessible databases, I use the “->” symbol to indicate that you must click on specific links on a given webpage. You will see this notation below.
Once logged into WRDS, the researcher is able to pull data from a variety of databases- COMPUSTAT, CRSP, etc. Log onto WRDS -> Compustat -> Global -> Fundamentals Annual -> Manuals and Overviews -> Compustat Global (FTP) Version Data Manual -> Auditors Codes
Per the first sentence on the page, it appears that “AUOP” is the variable we need and that it is concatenated and includes both the audit firm and the audit opinion.
We can also go to WRDS -> Compustat -> Fundamentals Annual -> Manuals and Overviews -> Global Plus EX NA Item List and save the Excel file with the definitions which is Global Plus EX NA Item List.xlsx. “AUOP” is also listed on the this Excel spreadsheet.
Compustat has an online menu feature where one does not need to connect to the “backend” directly with a SAS connection. To pull data in this way, the user browses through the online menu and clicks the variables needed. There is a circular question mark button the right of each variable which will pull up examples or definitions of the variable. Note that the menu list feeds from a specific SAS table on the backend of Compustat. If we want to know if a particular variable is available via the online menu, we can take a look at the contents of the SAS table and do a keyword search for the variable we want.
Home -> Compustat -> Global -> Fundamentals Annual -> Variable Descriptions
This shows us the contents of the “gfunda” table. If one searches for “AUOP” within this page, “AUOP” will not be found. This means that AUOP is not available via the online menu. Hit the back button on your browser, then – > Dataset list
Here we are seeing the actual SAS tables that are on the “backend” of WRDS. The online menu buttons link to these and pulls specific variables, however, there are more variables available on the backend than what appear to be available via the online menu.
Scroll down to Comp:Global: company [/wrds/comp/sasdata/global/company] -> G_CO_AAUDIT
Note that “au” is the auditor and “auop” is the auditor opinion. The data dictionary we pulled above made it look like “AUOP” was concatenated with both the auditor and the opinion, but it appears in the dataset that these are actually separate variables.
We need to log onto WRDS directly with SAS to pull the “AUOP” variable since it is not available via the menu pulldown. We can either log on to the backend and look at the full tables, or we can pull data directly via the SAS connection. First, we log on and view the table.
Open SAS and run intl_table_view.sas
The code for this is:
%let wrds=wrds.wharton.upenn.edu 4016;
options comamid=tcp;
signon wrds username=_prompt_;
libname intl “/wrds/comp/sasdata/global/company/” server=wrds;
options comamid=tcp;
signon wrds username=_prompt_;
libname intl “/wrds/comp/sasdata/global/company/” server=wrds;
Note that to view tables on the backend directly, the “libname” referencing statement is modified with “… server=wrds;” This modification is not present when we will pull data directly in the next example.
The first three lines of code above create the remote log in. The fourth line of code references the backend table and calls this table “intl”
-> Libraries -> Intl (this will be the name you gave the remote library and will show the blue box icon)
Now, we can see all of the backend tables in the directory. Nero 5 serial number free download. We click on G_Co_Aaudit and can see the records in the table. This is great, but to pull the data down onto our computer, we need a different program.
Open SAS and run intl_table_pull.sas
The code for this is:
%let wrds=wrds.wharton.upenn.edu 4016;
options comamid=tcp;
signon wrds username=_prompt_;
libname local’c:replicationpark’;
rsubmit;
libname intl”/wrds/comp/sasdata/global/company/”;
data intl_op;
set intl.g_co_aaudit (keep= gvkey indfmt datafmt consol popsrc rank au auop auopic ceoso cfoso datadate);
keep gvkey indfmt datafmt consol popsrc rank au auop auopic ceoso cfoso datadate;
procdownload data= intl_op out= local.intl_op;
run;
endrsubmit;
options comamid=tcp;
signon wrds username=_prompt_;
libname local’c:replicationpark’;
rsubmit;
libname intl”/wrds/comp/sasdata/global/company/”;
data intl_op;
set intl.g_co_aaudit (keep= gvkey indfmt datafmt consol popsrc rank au auop auopic ceoso cfoso datadate);
keep gvkey indfmt datafmt consol popsrc rank au auop auopic ceoso cfoso datadate;
procdownload data= intl_op out= local.intl_op;
run;
endrsubmit;
The blog platform separated the “set intl.g_co_aaudit…” line of code into two lines when I published this entry. These two lines of code are actually one line in my SAS code. Every line of SAS code must end with a “;”. If you see a line of SAS code that does not end with a semi-colon on this post, run it together with the line of code immediately below when you execute the file to avoid errors. You will see that this pulls a SAS dataset named intl_op.sas onto your computer. Next, use StatTransfer to convert the SAS dataset to Stata.
Note that we pulled the entire table. This is inefficient, because it is likely that we only want certain firms or certain years depending upon our research question and/or time period we wish to study. I am a Stata user and not a SAS user. There are ways to modify the SAS code to only pull specific firms, specific years, ignore duplicates etc. but I find that it’s often faster for me to pull everything and then clean the data up in Stata since I’m much more familiar with Stata code. What I have learned about SAS is purely from wading through help forums. With fast processing time, I find it’s easier to pull entire tables than to try and write the perfect piece of SAS code to get only what I really need.
Having said that, here is an example of pulling the audit opinions for international firms for years after 12/31/2000 and before 12/31/2002. I used the following pdf file to help me create this example:
Here is the code:
%let wrds=wrds.wharton.upenn.edu 4016;
options comamid=tcp;
signon wrds username=_prompt_;
libname local’c:replicationpark’;
rsubmit;
libname intl”/wrds/comp/sasdata/global/company/”;
data intl_op;
set intl.g_co_aaudit (keep= gvkey indfmt datafmt consol popsrc rank au auop auopic ceoso cfoso datadate);
where datadate > ’31dec2000’d & datadate < ’31dec2002’d;
keep gvkey indfmt datafmt consol popsrc rank au auop auopic ceoso cfoso datadate;
procdownload data= intl_op out= local.intl_op_years;
run;
endrsubmit;
options comamid=tcp;
signon wrds username=_prompt_;
libname local’c:replicationpark’;
rsubmit;
libname intl”/wrds/comp/sasdata/global/company/”;
data intl_op;
set intl.g_co_aaudit (keep= gvkey indfmt datafmt consol popsrc rank au auop auopic ceoso cfoso datadate);
where datadate > ’31dec2000’d & datadate < ’31dec2002’d;
keep gvkey indfmt datafmt consol popsrc rank au auop auopic ceoso cfoso datadate;
procdownload data= intl_op out= local.intl_op_years;
run;
endrsubmit;
Note the “where” statement keeps only observations with datadates after 12/31/2000 and before 12/31/2002.
For our second example, say we want to find which stock exchange non US-traded firms are traded on.
May 15, 2015 Download Allen Bradley software for free. RslogixMicro, RSEmulate and RSLinx. Steps to register and step to dowload. Allen bradley plc software free for windows 7. Software Allen-Bradley® software products support our intelligent I/O modules, portable data collectors, PowerFlex® drives, servo drives and motors, motor control. For information about Allen-Bradley PLC Simulation software, click here. Note: the links below are subject to change. If there is a problem with one or more of the links, please fill out our contact form at the bottom of this page. Jul 22, 2013 But it also contains the Allen-Bradley PanelView Component programming software, as well as a new editor to configure Rockwell Automation drives, including the new PowerFlex 525. Version 4 has so many new features that I would highly recommend upgrading to it if you have a previous version. Aug 22, 2013 Almost daily I'm asked about Rockwell Software (RS) and Allen-Bradley (A-B) software support for Windows 7. While I haven't memorized the list, the good news is Rockwell has put together an easy to use compatibility website which makes finding operating system support for it's software a.
WRDS-> Compustat -> Global -> Fundamentals Annual-> do a key word search for “exchange” “Stock Exchange Code” is found via this key word search. Click on the question box marker next to this item. It says that the “exchg” variable has the information needed and references the Compustat manual for the full list of codes.
To find a list of what the codes are,
WRDS -> Compustat -> Global -> Fundamentals Annual -> Manuals and Overviews -> Compustat Global (FTP) Version Data Manual -> Exchange Listing Codes
These codes are alphanumeric, but the examples given for the “exchg” variable per the online menu were not alphanumeric. We know the “exchg” variable starts with the letter “e”, so we can try to look in a different spot to see if we can find only numeric codes.
WRDS -> Compustat -> Global -> Fundamentals Annual -> Manuals and Overviews -> Compustat Global (FTP) Version Data Manual -> Data Definitions A- Fo
Search for “exchg” and we find “exchgi”, which also appears to be alpha numeric.
Let’s use the menu to pull data dates between Jan 2006 and Feb 2013. Select “Search the entire database” since we want all firms and not a subset. Click the “Company Name” and “Stock Exchange Code” variable boxes and select Stata as the dataset. Save the dataset on your computer as exchg_menu_pull.dta, open it, and type:
tab exchg
You will see that none of these codes are alphanumeric. They are purely numeric. At this point, the data definitions we have found do not appear to be useful for the exchange codes we have pulled from the online menu. However, there are SAS data dictionary files available on the “backend”.
WRDS -> Compustat -> Global -> Fundamentals Annual -> Dataset List
Look under the COMP:GLOBAL: dictionary: [ /wrds/comp/sasdata/global/dictionary ] heading.
Under this heading we see the G_R_Ex_Codes table. Let’s log on to view the table and see if this helps.
Run the exchg_table_view.sas file which executes the following code:
%let wrds=wrds.wharton.upenn.edu 4016;
options comamid=tcp;
signon wrds username=_prompt_;
libname exchg “/wrds/comp/sasdata/global/dictionary” server=wrds;
options comamid=tcp;
signon wrds username=_prompt_;
libname exchg “/wrds/comp/sasdata/global/dictionary” server=wrds;
-> Libraries -> Exchg -> G_r_ex_codes
These codes are purely numeric and also contain the data definitions for the exchange data we pulled earlier.
We can run a second SAS file to pull the table down. This SAS file is exchg_table_pull.sas, and runs the following code:
%let wrds=wrds.wharton.upenn.edu 4016;
options comamid=tcp;
signon wrds username=_prompt_;
libname local’c:replicationpark’;
rsubmit;
libname exchg”/wrds/comp/sasdata/global/dictionary”;
data exchanges;
set exchg.g_r_ex_codes (keep= exchgcd exchgdesc);
keep exchgcd exchgdesc;
procdownload data= exchanges out= local.exchg;
run;
endrsubmit;
options comamid=tcp;
signon wrds username=_prompt_;
libname local’c:replicationpark’;
rsubmit;
libname exchg”/wrds/comp/sasdata/global/dictionary”;
data exchanges;
set exchg.g_r_ex_codes (keep= exchgcd exchgdesc);
keep exchgcd exchgdesc;
procdownload data= exchanges out= local.exchg;
run;
endrsubmit;
Next, using StatTransfer we can convert the exchg.sas dataset into a Stata file, exchg.dta, and can merge it with exchg_menu_pull.dta.
Run dbases.do which merges these files together. This is a many-to-one merge based on exchg. The code for this .do file is:
capture log close
log using dbases.log, replace
cd c:replicationpark
use exchg.dta, clear
log using dbases.log, replace
cd c:replicationpark
use exchg.dta, clear
/* Rename the exchgcd variable to exchg so that we can merge.
It is a good practice to not overwrite raw datasets, so
once we modify the variable name the dataset is saved
as “exchg_merge.dta” */
It is a good practice to not overwrite raw datasets, so
once we modify the variable name the dataset is saved
as “exchg_merge.dta” */
rename exchgcd exchg
save c:replicationparkexchg_merge.dta, replace
use exchg_menu_pull.dta
/* merge the two datasets together */
merge m:1 exchg using exchg_merge.dta
/* We know that per the dictionary Ghana is exchg 100. Let’s
do a quick reasonableness check. */
do a quick reasonableness check. */
list if exchg100
/* We can see that the firm names appear to be firms in Ghana */
/* This is beyond the scope of this post
but it’s a good idea to always examine observations
that failed to merge. I will do a post on merging
sometime in the future. */
but it’s a good idea to always examine observations
that failed to merge. I will do a post on merging
sometime in the future. */
list exchg if _merge1
/* These didn’t merge because the exchg is a missing value in one of the datasets */
list exchg exchgdesc if _merge2
Dealscan Database Manual Pdf
/* These didn’t merge because they were not included in the
data we pulled from the online menu pulldown. */
data we pulled from the online menu pulldown. */