Cleaning Datastream downloads

Using ISIN codes is very often a good choice when you download data from Datastream and want to combine it with other data. ISIN codes are also regularly available in other databases. For the Datastream downloads you will have to seperate the ISIN again from the code of the variable which (as a default) is presented after the ISIN code between brackets.

Because every ISIN code consists of 12 digits, seperating them in Excel is easy: use the function =left(). Example: =left(CELL; 12)

In Stata you can use the substring command substr() to do something similar.
Example: gen isin=substr(Code, 1, 12)

Seperating the code of a company from the code of the variable in Datastream is more difficult if the company codes have varying lengths. The Datastream code (Datastream mnemonic) for instance, is such a code. If you need to seperate this from the code of the variable you need to make use of the length of the variable code which remains the same. To do this we combine to Excel functions: =left() and =len(). Example: =left(Cell; len(Cell)-11)

In Stata a similar thing can be done using a combination of substr() and strlen(). Example:
gen dscd=substr(variable, 1 ,(strlen(variable)-11))

Another thing which you may find necessary is seperating the names of the variables from the names of the companies. The variable name is most easily seperated since the name is a fixed number of characters. Again similar functions using the length of the content of CELLS can be used. A different way of seperating both is using a combination of the Left() function combined with the function Find(). For most downloads from Datastream both items are combined but there is a common element seperating the two, namely: ” “.
Using this we can build the following formula: =left(CELL;(FIND(” – “;CELL)))

In Stata a similar option is available to split a combined variable. The function split can be used as follows: split Name, generate(deel) parse(” – “). This generates two new variables that both start with the name deel and are numbered, for example: deel1 deel2. Afterwards you rename the variables deel1 and deel2 to what you need.


Using Stata to count a sequence

Not long ago a student asked me how to calculate the tenure for auditing firms that were attached to firms within a certain time frame for up to a specific year. The student was interested in the effect of auditor tenure on companies. The subject of auditor rotation has become interesting as a research subject in the field of accountancy as in some countries firms are required to change their Auditor every few years to ensure auditor independence.

Using the Stata program this can be done using a script to identify “spells”. My colleague Matthijs de Zwaan helped me with this and created an example script based on an article in the Stata Journal.
The script I made was based on the example and can be adapted to count other kinds of sequences in datasets.  I also added a few lines of code to show how a dummy could be created to identify if the auditor in the example Excel file was a Big Four accounting firm or not. The script can be downloaded here and looks as follows:

The end result (after removing suplicate data) gives the tenure of the last auditor (latenure) for each firm at the year 2008:

N.B.: In the .do file the location for all files is the I: drive. You may need to change the drive letter in the original script to (for instance) c: or H: to get it to run. Make sure both the script file and Excel dataset are in the same location.


Using Stata to count segments

At the end of March I got asked the question how to use Compustat North America segments data and get aggregated counts on business segments or geographic segments. The variable business segments was to be used as an indicator of diversity: how many different types of activity a company included in it’s activities. The Geographic segments was to be used as an indicator on how widespread these activities were geographically for each company.

Specific important commands that are needed:

generate year=year(datadate) > using this command you get a year which can be used to count instances of segments. This is only needed if no available year can be used (like fiscal year / fyear).

drop > using this command you delete all variables that are non-essential from the dataset

order gvkey year > this command sorts the dataset first on the gvkey (= global company key which uniqely identifies a company in any Compustat database) and then by year

duplicates drop > this command deletes any possible duplicate annual data. This is important as the count only involves unique segments

by gvkey year: egen segmentcount = count(sid) > this command generates a new variable (segmentcount) and gives it the value of the count of the segment id codes (SID) for each company and individual year.

To later combine the business segments count dataset with the geographical count dataset a unique ID (UID) is created to later merge the datasets again into a single dataset.

Overall the script (.do file) I created does three things:
1) It creates a new dataset with business counts
2) A dataset with Geographical counts is made
3) It merges both newly created datasets into a single dataset

Example script screenshot:

The example dataset with .do script file can be downloaded here.

Example result screenshot:

N.B.: In the .do file the location for all files is the U: drive. You may need to change the drive letter in the original script to (for instance) c: or H: to get it to run. Make sure both the script file and Stata dataset are in the same location.


Stata & large downloads from Datastream

In the past I demonstrated how it was possible to change a dataset from a wide presentation (= data in columns) to a long presentation (= data in rows) using the reshape command in Stata. This option works fine where smaller datasets are concerned and if there is no choice when downloading data. For larger datasets, however, it may become a problem to download the data in wide format and also the Stata software may have difficulty changing the presentation.

If you download, for instance, daily price data from Datastream for many years (using ISIN codes) for x number of listed companies the wide download format (transposed) is not a good option. Try downloading everything without transposing and you get everything in long format as follows:

Changing the data to a proper long format for Stata goes as follows:

1) Make a copy of the original data and add a new row at the top.
2) Above the Dates in column A type in a general name like “company” in Cell A1.
3) Also use numbers for each individual company at the top.
4) Insert a new row above the data and give the name Date in Column A above the actual dates
5) In the cells of this same row use an Excel formula to generate fake names. Example: =$A$1&B1
The copied Sheet should look as follows:

6) Now also make a copy of the first 3 rows and paste the data transposed using the Right-click option in a new sheet. Example:

7) Use the Excel function =left() to get the ISIN codes (12 digits) in a separate column or use Search and replace to get rid of the variable indicator (P)

8) Copy and paste this data as values to a new sheet. It should look as follows:9) Make a new copy of the first changed sheet and delete the first few rows. Make sure to copy & paste as value (right-click) to remove any formulas. The sheet should now look as follows:

Now start up Stata en use the sheets in the Excel Worksheet step by step:

10) First you import the sheet (from step 9) with the price data from Excel using the command import (or the Menu option File > Import > Excel spreadsheet).

11) Use the reshape command the rearrange the data to long format: reshape long company, i(Date) j(price). Change the variable names and make sure that the column with numbers is called company. Next save this as a Stata database.

12) Use clear all to start with the second sheet. Now import the second sheet with the company ISIN codes from step 8.

13) Use the merge command to merge this data with the Stata database we created at step 11). The command would look as follows:
merge 1:m company ” c:\ … \filname.dta”, sheet(…), firstrow

14) now do some data curation steps to change prices to numbers (destring command) and generate a newly formatted date. Make sure to save this file.

As an example I provide here a zip-file with an example download in Excel with the Steps on separate sheets and a .do file that can be run step by step. If you want to use this, be sure to extract both files to the folder c:\temp on your computer. You can also extract them in a different folder. In that case you need to change the locations of the files in the .do file before running the file in Stata.

Important to remember with this example: Stata may still run into problems if you have hundreds of companies (or more) for which you have daily data for many years. If there are problems I recommend converting the download in brackets of 100 to 200 companies and then appending the resulting databases to create a single Stata database.

N.B.: A student pointed an example of this out to me which he found at: Princeton University Library Data and Statistical Services. I do not claim any credit for the original idea but the Stata example provided on this blog was made by me.


Stata & missing or duplicate data

When you work with large datasets or big data it may happen that after working with it for some time you need to take a good look at what has happened to the data. Especially if you work with combinations of datasets and/or work on it with more people. Another instance is: when you have received the dataset from a researcher or organization and need to remove superfluous data that may not be relevant to your own research.

1) Investigate the data
There are a few simple commands in Stata that provide a good overview:

  • desc or describe = this command provides a brief summary of the entire dataset
  • summ or summarize = another fine command that gives a quick overview of all the variables with information on: number of observations, the mean, standard deviation, and the lowest and highest values (min & max)
  • tab or tabulate = a good way to cross-reference several items and see whether there are any obvious outliers or patterns in the data

These and many more commands or combinations of commands allow you to watch and judge the data.

2) Missing data

  • Using the summ command it was easy to see that some fields had no data. In this case it may be a good idea to delete them as they serve no purpose here. You can delete a variable/field by typing drop variable. For example: drop CIKNew. A range of variables next to each other can also be dropped with a single command. For this example: drop indfmt – conm. There are many more options to delete entire variables/fields from a dataset.
  • Another way to clean data can be applied if you require only those observations/records that (for crucial variables) do not have missing values/data. Deleting observations can be done using the missing value command: drop if mi(variable). For example: drop if mi(Totaldebt). The Stata result screen will show the result of this action: number of observations deleted.
  • Deleting missing values is, however not always straightforward. Stata shows missing values as dots if you view a dataset with the browse command. In some datasets, however, missing values may sometimes (partially) be represented by another value in some observations. If this is the case it is a good idea to replace some of these values first to allow for easier editing/deletion. If in your dataset the number zero indicates the same thing as a missing value (in some records) you can use mvdecode to replace them with a dot (= how Stata usually represents missing values). The command would look like: mvdecode variable, mv(0=.). Afterwards you can the remove all missing values the usual way with drop.

3) Removing duplicate data
When you are using multiple datasets and have combined them you could have some duplicate observations. Using data from some specific databases may also get you unintentional duplicate data. In Compustat you run the risk of duplicates if, for instance, you only need data for industrial type companies but, when doing the search in the Fundamentals Annual database you forget to unmark the option FS at the screening options at Step 2 in WRDS. Some companies have more than one statement in Compustat for the same fiscal years and will get you both FS and IND type/format statements.
The Stata command to remove duplicates should be chosen carefully. I usually combine a unique ID code with a specific event year or date. For instance: duplicates drop CIK year, force


  • duplicates drop removes duplicates
  • in this example duplicates are identified by the combination of the variable CIK (ID code = Central Index Key) with the variable year
  • duplicates will be removed without warning by including the las bit:
    , force

Personally I think removing duplicates without first checking may not always be the smart thing to do. If you are working with a large dataset it may be a good idea to first tag possible duplicates and then have a look before removing these. The command to tag the duplicates is: duplicates tag, gen(newvariable). This command checks the whole dataset with all variables for all observations for duplicates and stores the result as a number in the new variable with the name newvariable.

Another version of removing duplicates may have to do with the number of necessary observations by entity in a dataset. In some cases an analysis requires a minimum number of observations/records to be relevant. If there are too few observations you may again remove them only, in this case it can be done using the count function on the entity (for example a company identifier like ISIN, CIK, or GVKEY). You do this as follows:

  • Sort the dataset on the ID that will be counted. Example command: sort CIK
  • Now count the number of ID’s in the dataset and store them in a variable. Example command: by CIK: egen cnt = count(year). What this does is count the times each CIK ID occurs by counting the years and stores the count/number of years in the new variable cnt.
  • We can now remove observations of entities for which the count (of years) is below the number stored in the variable cnt. Example command: drop if cnt<10. This means that we need a minimum of 10 observations for an entity.

N.B.: A few final remarks on handling missing data concern the way you work with the data. When you are performing such cleaning actions as described above it is a good idea to first make a copy of your database before you do all this and save the actions as there is no undo like in many programs. You can also experiment a bit with a copy and you should definitely save the actions that you choose the finalize in a Do-file and when yiou continue from there again start with a copy. To keep track of your versions of the database you can fut a date in the name of each version. When you work with much data over a long time it is also a good idea to save space and memory by compressing the database with the command: compress. Some variables will then be changed to save space.


Stata & date formats

The past few weeks I have been learning about and working with Stata. This program can handle a lot of data and uses commands to edit data or analyse it. There are many commands available and one command is very handy when it comes to changing date formats.
When you work with data and intend to do an event study using the Request Table option from Datastream you may have to change the date format for the event dates to something that Datastream understands (also depends on the date format used by the computer).

In Excel you can use the command =TEXT(A1,”dd-mm-yy”). The Dutch version looks similar: =TEKST(A1;”dd-mm-jj”)
Afterwards you still have to copy and the paste as values to get workable dates for Datastream.

Now if you are working with a dataset in Stata you can use a command to do a similar thing: format [variablename] %tdDD-NN-YY

The elements mean:
format = change the format of a variable
variablename = indicates the date/variable item you wish to change
%tdDD-NN-YY = indicates that the timedate variable should look like 17-02-99. DD = day with two digits, NN = month with two digits and YY = Year with two digits.

Example of dataset before the change:

Now I use the command to change the look of variable dateff: format dateff %tdDD-NN-YY. The result looks as follows:

If you look closely you see that “under water” the variable still looks as it is originally presented: the first date 20-08-07 still looks like 20aug2007. On the right side you also see that dates in Stata are actually variables of the type Integer = a number that can be written without a fractional component (Wikipedia).
Interesting sidenote: in Stata dates are actually integer numbers, just like in Excel. Where in Excel number 1 represents 1-1-1900 in Stata the number 1 represents 1-1-1960. Negative integer numbers in Stata represent older dates. When you work with older dates it is wiser to use 4-digit year formats to avoid confusion.

There are a lot more options to change the dateformat. Another example that may be useful for using with the Event Tool in the CRSP database is:

format variablename %tdCCYYNNDD

In this instance dates will be formatted with 4 digits for a year, for example: 19871022

If you want to know all the options for formatting dates you can use the Help command: help datetime display formats.

Important: If you export a formatted file from Stata to an Excel file you will lose the formatting for the dates you just did. If you wish to keep the dates with the (changed) formatting you need to export/save the file as a (delimited) text file using the command: export delimited filename.txt and then open the text file in Excel using the text wizard. Also remember to open the formatted date column as text to keep the formatted date in tact!

This example applies to Stata editions 13 and 14.


Changing Datastream data & Stata

The past few weeks I have been learning about and working with Stata. This program can handle a lot of data and uses commands to edit data or analyse it. A sequence of commands can be saved in .do files and then rerun as a script. There are many commands available and one of them is very handy when it comes to changing data from columns to rows. It is similar to the transpose option that Microsoft Excel offers for quick changes. It is the reshape command.

In this blog post I will use the reshape command to change Datastream data as an example. Similar work can be done for other downloads from databases like Amadeus or Bankscope.
For downloads it can take a bit of work to change the data and rework it before Stata can be used to merge it with other data. In the case of Datastream this has everything to do with the fact that Datastream does not repeat an ID for each year of data you download (unless using a Request Table search). Example screenshot original download from Datastream (transposed):

The following changes need to be made in Excel:

1) Get the ID back from the download without the extra Datastream codes. This can be done using the function MID(Cell,Start,Number) (= in Dutch versions of Excel: the Deel() function). MID() allows you to get part of the contents back from a Cell. Example:

2) Next you need to create column year headers that start with a text character and then the year of the column (this step is important for Stata later). It can be done smartly using a specific cell for the header and then combining it with the original year above each column as follows with the formula =($A$1&(B$1)). The dollar signs fix certain cells or a row (or column). Example:

3) Now save the Datastream Excel file without these formulas. If necessary use copy > paste special > Values

4) Next we start up Stata and get the file. In this case we prepared the Datastream file nicely and can therefore use the command: import excel using DS-Prepared.xlsx, firstrow
The firstrow option tells Stata that this row has the variable names and lables. Example:

5) When we look at the data with the browse command you see that the data looks basically the same as how it appeared in Excel:

Only two things are important here:

  • The data is not yet formatted as we need it: the numerical data are now strings (red) and need to be changed later
  • To later merge data the company ID’s need to be combined with the years

6) We now transpose the data using the command Reshape. This command has many options and allows you to rework tables. If you need more explanation on how it works you can type the command help reshape and Stata will provide much information on how to use the command. For this example the command is:

reshape long Y, i(CompanyCode) j(year)

In a nutshell this tells Stata that the years for the columns need to be repeated for the observations/records and can be found in the names of the variables. The ID for each company also needs te be repeated for the years of all observations. Example result:

When you use the command browse again you see the result of the data change:

7) We now need to create a unique ID combination to later merge the data. To do this we need to turn the years back from numerical values into strings using the tostring command: tostring year, replace. The final step is creating the unique ID combination UID with the command: gen UID = CompanyCode+year

8) Now there are a few steps left to finalize the Datastream data but not all of it is necessary. One thing is necessary: for Stata the numerical data is still a (text) string and now need to be changed. Use the destring command to as follows: destring Y, replace force

Destring also removes any values that were not numbers (but text) and replaces them with an empty cell (.) because of the option force (this can be dangerous if the content of a cell is a combination of a numerical value and text).

Optional stuff:

  • Use the drop command to remove unnecessary columns
  • Remove observations without data using, for example: drop if mi(Y)
  • The command order can be used to reorder data and put the UID variable in the first column
  • The command rename can be used to rename variables

The end result could look as follows:

To make stuff easier you could create a .do file that contains all the commands in sequence and this allows you to reuse it in similar situations for similar downloads. Here you can download an example .do file that shows examples of all the commands.

This example applies to Stata editions 13 and 14.