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.


WorldScope coverage update 1st Quarter 2016

WorldScope company records now cover annual reports data for 79919 companies. This includes 47392 active and 32527 inactive companies. This update: 732 companies were added. Because of these additions the number of active companies in the database has gone up 385 from 47007 in December. WorldScope company records are also available through Datastream and LexisNexis.

Today I have updated the WorldScope country coverage file and it now includes the latest update as it was posted in the first Thomson Reuters Infostream quarterly publication of 2016.

Major updated Countries (new records):
Australia (41)
Germany (19)
Hong Kong (47)
India (36)
Japan (31)
South Korea (27)
Sweden (35)
Taiwan (29)
Ukraine (27)
United Kingdom (27)
United States (174)


SDC Platinum save problem

In some SDC databases it may happen that you get an error when you are exporting / saving data. Most commonly you encounter this problem when you try to use a custom report. The database where you most often get problems is the VentureXpert part database that has information on funds and firm investments. The error that pops up is “Invalid column“. See example:

The problem may be related to the output option that was chosen. There are just two choices for output: Excel and Text (= plain, fixed width). When you are downloading much data the text choice is usually the best option because the (old) SDC software works with the limits of the old(er) Excel editions and Windows. Also, the text download is much faster than the Excel download. By right-clicking the report you can change the output to text format. In addition, you also need to change the Options. See example:

In the options tab “Titles and Options” you need to mark the option Text (instead of Columnar Grid). See example:

After the changes you get a plain (fixed-width) text file that may be used in programs like Excel, Stata and SPSS.


Datastream search change

Not long ago the Datastream series search screen was changed, maybe as part of the overall move to Internet Explorer 11 (last version supported for IE 7 and 8) and the new Microsoft Edge browser. The main change involved the removal of the search option Criteria Search. This advanced search option allowed you to combine search criteria and create lists of series and these lists could also more easily be saved.
Example screen shot of the previous search screen:

The advanced criteria search screen was identical to the old search website that existed before the current simple search screen. Example Criteria Search screen:

Most of the search options or save options are available in the current simple search screen (or through the Explore option at the top) but it requires more work if you are a researcher and need many series from different markets etc. Large selections are also more difficult as the save and selection options are now limited. Overall, in most cases, it still offers enough search or limiting options:

N.B. 1: I contacted the Thomson helpdesk about the change and asked if the Criteria Search option could be restored. Unfortunately I first got excuses stating that this had to do with the switch from IE 10 to IE 11 (where I work). When I tested this using IE 10, however, I got the same altered search website. The technical people eventually indicated that we were stuck with the change and they were disinclined to restore this handy option. Maybe it is part of their overall planned move to stimulate people to start using the Eikon platform.

N.B. 2: The Extranet option for Datastream is also changed and now you are puched towards the Thomson Infobase website. So far I do not think this is much of an improvement as some of the information I used to be able to find seems to be gone there. Example: