Category Archives: Uncategorized

The Error option matrow() not allowed” when using asdoc with tabulate command

Category:Uncategorized

Ronald asked the following question: I am getting the error when using asdoc with tabulate command.
option matrow() not allowed
r(198);

I asked Ronald to send his dataset for a closer look. Upon inspection, it turned out that Ronald was trying to tabulate values of a string variable, and asdoc had problem with that. As a temporary solution, I suggested the following two steps.

Step 1: Convert the string variable to numeric variable with value labels. The good news is that we can do that in one line of code using the encode command. So assume that the string variable name is country, we shall encode it:

encode country, gen(country_new)

where country_new is a new is a new variable with value labels.

Step 2: Now we can use it with asdoc, so to tabulate it and send the output to MS Word, we type:

asdoc tab country_new, replace

Merging datasets in Stata on long strings and less precise matching criterion

Category:Uncategorized

The Problem

Consider the following two datasets where company names are not exactly the same in both the datasets, still we want to merge them using the name variable as the merging criterion. How that can be done in Stata.

name in dataset 1	name dataset 2	symbol data set2
The Pakistan General Insurance Co. Ltd.	The Pakistan General Insurance	PKGI
Security Leasing Corporation Ltd.	Security Leasing Corporation L	SLCL
NATIONAL BANK OF PAKISTAN	NATIONAL BANK OF PAKISTAN	NBP
THE BANK OF KHYBER	THE BANK OF KHYBER	BOK
THE BANK OF PUNJAB Ltd	THE BANK OF PUNJAB	BOP
SILKBANK LIMITED .	SILKBANK LIMITED	SILK
Awwal Modaraba Comay	Awwal Modaraba	AWWAL
B.F. Modaraba.	B.F. Modaraba	BFMOD

The above table shows that the company names not only differ in terms of different number of characters but also in terms of capitalization. However, there is a general patterns of similarity in the first few characters, starting from left to right. Given that, we can split the problem into two parts.

 

1.Extract similar characters

Extract the first few characters that are similar in both the dataset and merge the data using those similar characters. For example, in the second row of the above table, “The Pakistan General Insurance” part is similar in both the tables. If we count these characters, they are 30. So how exactly are we going to find the matching number of characters in each case? We shall not do that. Instead, we shall take the iterative path where:

1. we start with extracting the first n-number of characters from both the key variables in the two datasets and merge using the extracted (truncated) variables. If the merge succeeds, we shall save the merged data separately. Also, we shall delete, from the initial file, those records which were successfully merged, and further process those that did not merge. In this first step, we shall normally start with extracting a large number of characters, for example, up to 30 characters in the case of row 2 of the above table.

Please note that the relevant Stata function is substr() for extracting a given number of characters from a variable. We shall discuss it further as we proceed in this article.

2. In the next iteration, we shall further process those records which did not merge. This time we shall extract one character less than what we used in the preceeding step. The idea is that if two variables did not have the first 30 characters in common, they might have the first 29 characters in common. But this time, we need to be careful as we reduce the extraction of the initial number of characters, the chances of matching incorrect records increases. For example, consider the following two records where the first 12 characters are exactly the same in both the records i.e. “THE BANK OF “, therefore, if we merge the two data sets using only the first 12 characters, we shall incorrectly merge THE BANK OF KHYBER into THE BANK OF PUNJAB

THE BANK OF KHYBER

THE BANK OF PUNJAB

So using the first 29 characters of the two variables, we shall proceed to merge. Again, we shall retain the successfully merged records, append it to the already saved successful merged data, and delete it from the initial dataset, just we did in the first step above.

3. The iterative process will continue in the same fashion, and each time we need to pay more attention to the merged data to identify any incorrect merges as explained in step 2.

4. We shall stop at a point where the data seems to have a reasonable number of similar observations based on the initial characters.

 

2. Use lower case

The second part of the problem is the dissimilarity in the capitalization of the names. This part of the problem is easy to handle as we can use the function lower() to convert all the names to lower cases and then use it in the merging process.

 

An Example

Let’s use a simple example to implement what we have read so far. In this example, we are going to use the same data as given in the above table. From that data, we shall create two datasets. One of the dataset will remain in the Stata memory, we shall call it data_memory. The other data set will be saved to a file, we shall call it data_file. The data_file has two variables, name and symbol. We shall merge the data_memory into data_file using variable name as the merging criterion. To create the two dataset, we can copy and paste the following code to Stata do editor and run it.

* Copy the following code and run from Stata do editor
 
*-------------------------------------------------------------------------- 
*                        Create the  data_memory 
*-------------------------------------------------------------------------- 
clear
 input str30 name str5 symbol
 "The Pakistan General Insurance" "PKGI" 
 "Security Leasing Corporation L" "SLCL" 
 "NATIONAL BANK OF PAKISTAN"      "NBP"  
 "THE BANK OF KHYBER"             "BOK"  
 "THE BANK OF PUNJAB"             "BOP"  
 "SILKBANK LIMITED"               "SILK" 
 "Awwal Modaraba"                 "AWWAL"
 "B.F. Modaraba"                  "BFMOD"
 end
save data_memory, replace

*--------------------------------------------------------------------------
*                          Now to create the data_file
*-------------------------------------------------------------------------- 
clear
 input str39 name
 "The Pakistan General Insurance Co. Ltd."
 "Security Leasing Corporation Ltd."      
 "NATIONAL BANK OF PAKISTAN"              
 "THE BANK OF KHYBER"                     
 "THE BANK OF PUNJAB Ltd"                 
 "SILKBANK LIMITED ."                     
 "Awwal Modaraba Comay"                   
 "B.F. Modaraba."                         
 end
 
save data_file, replace 

Now that we have created the two datasets, let’s start the process step by step. In the following code box, the code perform the first step as discussed above. I shall then explain what actually the code does.

use data_file, clear
gen name2=lower(substr(name,1,30))
clonevar name_old = name
save data_file, replace
use data_memory
gen name2=lower(substr(name,1,30))
merge 1:1 name2 using data_file
save temporary, replace
keep if _merge == 3
save matched, replace
list name name2 symbol
+----------------------------------------------------------------------+
            name                                    name2              |
---------------------------------------------------------------------- |
NATIONAL BANK OF PAKISTAN                 national bank of pakistan    |
Security Leasing Corporation Ltd.         security leasing corporation | 
THE BANK OF KHYBER                        the bank of khyber           |
The Pakistan General Insurance Co. Ltd.   the pakistan general insurance 
-----------------------------------------------------------------------


use temporary, clear
keep if _merge == 2
keep name
save remaining, replace
list 
 
 +--------------------+ 
 |     name           |   
 +--------------------+  
 |     Awwal Modaraba |
 |      B.F. Modaraba |
 |   SILKBANK LIMITED |
 | THE BANK OF PUNJAB |
 +--------------------+  

 

Explanation

In the above code block, we loaded the first dataset i.e. data_memory and created a truncated variable (name2) from the original variable using the following line of code. The first function in this line is lower() that converts all cases to lower cases. The second function is substr() that extracts bytes 1 to 30 from the string variable name. So this line will convert “Security Leasing Corporation Ltd.” to “security leasing corporation“

gen name2=lower(substr(name,1,30))

After creating the name2 variable, we saved and replaced the data_file. We then loaded the other dataset, that is, data_memory and created a similar truncated variable as above, and then merged the two datasets using this truncated variable. The merged dataset has all useful information and we saved it in a temporary.dta file for further processing.

The first step in this processing is to isolate successful merges, that is flagged by the _merge == 3 code, therefore, we kept all such observations and saved them to matched.dta file. The list command shows that we have 4 successful merges in this go. We then reload the temporary.dta file to isolate those records that did not merge. Therefore, those records are saved to another file, which we named as remaining.dta.

In our next iteration, we shall take all of the above steps using the remaining.dta file, instead of data_file.dta file.

 
*------------------------------------------------------------------------- 
*                        Iteration 2 - using first 29 characters
*-------------------------------------------------------------------------  
use data_file, clear
drop name2
gen name2=lower(substr(name,1,29))
save data_file, replace
use  remaining, clear
gen name2=lower(substr(name,1,29))
merge 1:1 name2 using data_file
save temporary, replace
keep if _merge == 3
save matched, replace
list name name2 symbol

 +--------------------------------------------------+  
 |               name                name2   symbol |  
 |--------------------------------------------------|
 |     Awwal Modaraba       awwal modaraba    AWWAL |
 |      B.F. Modaraba        b.f. modaraba    BFMOD |
 |   SILKBANK LIMITED     silkbank limited     SILK |
 | THE BANK OF PUNJAB   the bank of punjab      BOP |
 +--------------------------------------------------+ 

save remaining, replace

 

Is there a simple way to do this?

 

smerge Program

The above tutorial is best for a learning the merging process, however, it is too lengthy and is not optimal for quick applications. I have managed to get the code in a standard Stata ado program. I call this program as smerge, that is merge based on sub-string.

 

Installation

The program can be installed by typing the following line in Stata command window

net install smerge, from(http://fintechprofessor.com) replace

The syntax of the program is given below:

 smerge varname, use(file_name) [lower() higher()]

smerge is the program name, followed by the varname, that is the variable name which should exist in both the files, i.e. the file currently loaded file, i.e. the file in the memory of Stata and the file on disk. After the comma, the program options are listed. Option use(file_name) is a required option. In the brackets, we have to provide the file name with which we want to merge the data. It can be a full directory path with the file name or simply the file name if the file is in the current directory.

options enclosed in brackets [] show optional options in Stata. There are two optional options in smerge program. If these options are not specified, then the default values are used, which are 30 for the higher and 10 for the lower. The higher() option sets the initial value of the number of string bites to be extracted from the varname to make a truncated variable that will be used for the first iteration in the merge process. The initial value is decremented by one in the next iteration until it reaches the limit set by the option lower().

 

An Example using smerge

Let use the same dataset which we created above. We shall first load the file data_memory and merge it with the file data_file.dta. Both the datasets have the variable name.

use data_memory.dta, clear
smerge name, use(data_file) lower(10) higher(30)
list

. list
 +---------------------------------------------------------+
 |                                    name   symbol   nstr | 
 |---------------------------------------------------------|
 | The Pakistan General Insurance Co. Ltd.     PKGI     30 |
 |       Security Leasing Corporation Ltd.     SLCL     30 |
 |               NATIONAL BANK OF PAKISTAN      NBP     30 |
 |                      THE BANK OF KHYBER      BOK     30 |
 |                  THE BANK OF PUNJAB Ltd      BOP     18 |
 |---------------------------------------------------------|
 |                      SILKBANK LIMITED .     SILK     16 |
 |                    Awwal Modaraba Comay    AWWAL     14 |
 |                          B.F. Modaraba.    BFMOD     13 |
 +---------------------------------------------------------+ 

 

Explanations

Read the second line in the above code block. We typed smerge and then the variable name. The name variable is the key variable that we are using as the merging criterion. Option use(data_file) tells smerge program to merge the current data in memeory with the file data_file. And option higher(30) and lower(10) sets the limits for sub-string extraction for creating the truncated variables that will be used for merging. And that’s it. We just need one line of code to do all the steps that we went through in the earlier tutorial.

The list command shows three variables, one of them is nstr. This variable records the number of strings from left to right of the varname. This number is recorded when the merge is successful. So the list command shows that the four records were merged in the first iteration i.e when we extracted the first 30 characters. The 5th record was merged when we extracted 18 characters i.e. The full entry is THE BANK OF PUNJAB Ltd, and the first 18 characters are THE BANK OF PUNJAB

asdoc: Export Stata dta file to MS Word

Category:Uncategorized

Creating tables in Stata using asdoc is super-easy. In this short post, I’ll show how to use asdoc to export Stata data to MS word files. If you have not already installed asdoc, it can be installed by

ssc install asdoc

 

For exporting values from the data files, we can use the sub-command list of asdoc. We can also make the command conditional using the in and if qualifiers. In the following example, let us use the auto data set from the system folders

sysuse auto, clear

asdoc list price trunk mpg turn in 1/10 , replace

 

 

Explanation

In the above line of code, we wrote asdoc and the sub-command list. After that, we specified the names of the variables that we wanted to export to the MS word document. These variables included price trunk mpg turn. After that, we used the phrase in 1/10, that is the in qualifier to report observation 1 to 10. The option replace will replace any existing output file with the name Myfile.doc

 

ASROL Version update: calculation of geometric mean and products in a rolling window and over groups in Stata

Category:Uncategorized

 

Description

asrol calculates descriptive statistics in a user’s defined rolling-window or over a grouping variable. asrol can efficiently handle all types of data structures such as data declared as time series or panel data, undeclared data, or data with duplicate values, missing values or data having time series gaps. asrol can be used for the calculation of a variety of statistics [see table of contents].

 

Installation

ssc install asrol

After installation, you can read the help file by typing:

help asrol

 

Options and defaults

Version 4.5.1 of asrol significantly improves the calculation of the product and the geometric mean.  Since both the statistics involve the multiplication of values in a given window, the presence of missing values and zeros present a challenge to getting the desired results. Following are the defaults in asrol to deal with missing values and zeros.

b. Handling zeros in geometric mean: To be consistent with Stata’s default for geometric mean calculations, (see ameans), the default in asrol is to ignore zeros and negative numbers. So the geometric mean of 0,2,4,6 is 3.6342412, that is [2 * 4 * 6]^(1/3). And the geometric mean of 0,-2,4,6 is 4.8989795, that is [4 *6]^(1/2)

c. Handling zeros in products: Zeros are considered when calculating the product of values. So the product of 0,2,4,6 is 0

d. Option ignorezero: This option can be used to ignore zeros when calculating the product of values. Therefore, when the zero is ignored, the
product of 0,2,4,6 is 48

e. Option add(#) : This option adds a constant # to each values in the range before calculating the product or the geometric mean. Once the
required statistic is calculated, then the constant is substracted back. So using option add(1), the product of 0,.2,.4,.6 is 1.6880001 that is
[1+0 * 1+.2 * 1+.4 * 1+.6] – 1 and the geometric mean is .280434 is [(1+0 * 1+.2 * 1+.4 * 1+.6)^(1/4)] – 1.

 

Examples

Let us start with simple examples of calculating the geometric mean and products.  Our example data has stock prices, company identifiers (symbols) and time identifier (date) 

use http://fintechprofessor.com/stocks.dta, clear

* Generae numeric identifier for each firm
encode symbol, gen(id)

* Declear the data as panel data
tsset id date

* Create stock returns
gen returns = d.close/l.close

* Note the above formula for stock returns is analogous to
gen returns2 = (close - L.close) / L.close

 

 Geometric mean

 Now find geometric mean for stock returns, adding 1 before calculation and subtracting the same after calculation.  The calculations are made for each firm in a rolling window of 20 observations

bys id: asrol returns, stat(gmean) window(date 20) add(1)

 

Products – the case of cumulative returns

Since we find products of (1+returns) for finding cumulative returns over n-periods, we can use the product function of asrol [read this blog entry for more more details on simple and log returns. 

Cumulative n-period simple returns =(1+simple_r1) * (1+simple_r2) 
*(1+simple_r3)  ... (1+simple_rn)  - 1     --- (Eq. 1)

 

The asrol command for the 20-periods rolling window cumulative returns would be:

bys id: asrol returns, stat(product) window(date 20) add(1)

 

 

Option to ignore zeros

Option ignorezero or ig can be useful when we want to exclude zeros from the calculation of products. So let’s say we have the variable x that has values of 1, 2, 3, 0, and 5. Finding product of this variable will result in zeros. If there were circumstannce where we wish to find product of only non-zero values, the asrol command would be

asrol x, stat(product) ig
list

+--------------+
| x   produc~x |
|--------------|
| 1         30 |
| 2         30 |
| 3         30 |
| 0         30 |
| 5         30 |
+--------------+

Without using the option ig, the product would be zero

asrol x, stat(product) gen(pro_withoutig)
. list
+-------------------------+ 
| x   produc~x   pro_wi~g |
|-------------------------|
| 1         30          0 |
| 2         30          0 |
| 3         30          0 |
| 0         30          0 |
| 5         30          0 |
+-------------------------+

 

A note on the methods used

Previous versions of asrol used log transformation of values for finding products and geometric mean. Version 4.5.1 onwards, the log transformation method is discontinued in the calculation of products and geometric means. The calculations now consider actual multiplications of the values in a given range. So the geometric mean is calculated as the nth root of the products of n numbers. 

or a set of numbers x₁, x₂, …, x_n, the geometric mean is defined as

where the capital pi notation shows a series of multiplications.

Similarly,  the products are calculated as :

Product = x1 * x2  ... xn

---

 

Quick Table for Converting Different Dates to Stata Format

Category:Uncategorized

Daily Dates

15/1/2015

gen mydate=date(text, "MDY")

15feb2015

15/1/2015

gen mydate=date(text, "MDY")

15feb2015

2015/1/15

gen mydate=date(text, "YMD")

15feb2015

201502

gen mydate=date(text, "MY")

1feb2015

1/15/08

gen mydate=date(text,"MDY",1999)

15jan1908

1/15/08

gen mydate=date(text,"MDY",2019)

15jan2008

1/15/51

gen mydate=date(text,"MDY",2000)

15jan1951

1/15/01

gen mydate=date(text,"MDY",2050)

15jan2001

1/15/00

gen mydate=date(text,"MDY",2050)

15jan2000

20060125

gen mydate=date(text, "YMD")

25jan2006

060125

gen mydate=date(text, "20YMD")

25jan2006

* Enter example data
clear
input str9 text
"15/1/2015"
end

* Now convert the variable text to Stata date
gen mydate=date(text, "DMY")

* Change the display format
format mydate %td

weekly

gen weekly_date = wofd(daily_date)

Monthly

gen monthly_date = mofd(daily_date)

Quarterly

gen qyarterly_date = qofd(daily_date)

Yearly

gen year = year(daily_date)

* Enter example data
clear
input str9 text
"15/1/2015"
end

* Now convert the variable text to Stata date
gen daily_date=date(text, "DMY")
format daily_date %td


* Create a weekly date
gen weekly_date = wofd(daily_date)
format weekly_date %tw

* Create a monthly date
gen monthly_date = mofd(daily_date)
format monthly_date %tm

* Create a quarterly date
gen quarterly_date = qofd(daily_date)
format quarterly_date %tq

* Create a yearly date
gen year = year(daily_date)

weekly

2018w46

gen daily_date = dofw(given_date)

Monthly

2018m11

gen daily_date = dofm(given_date)

Quarterly

2018q4

gen daily_date = dofq(given_date)

Yearly

2018

gen daily_date = dofy(given_date)

Weekly to monthly

2018w46

gen monthly_date = dofm(dofw(given_date))

Monthly to weekly

2018m11

gen weekly_date = dofw(dofm(given_date))

Quarterly to monthly

2018q4

gen monthly_date = dofm(dofq(given_date))

Monthly to quarterly

2018m11

gen quarterly_date = qofd(dofm(given_date))

Weekly to quarterly

2018w46

gen quarterly_date = qofd(dofw(given_date))

Quarterly to Weekly

2018q4

gen weekly_date = dofw(dofq(given_date))