Computing Z-score using Unbalanced Panel Data

[Tony Annan]

Hi, I am attempting to create a new variable RAROAit = ROAit / SD(ROA)ip for an Unbalanced Panel dataset, where i is the panel id variable, t is the time variable, and SD is the standard deviation of the variable ROA. SD is calculated based on observations for the period p defined as t to t-2. How do I generate this variable RAROA efficiently in STATA, given the definition of p?. I'm new to STATA. Can anyone assist me?

[Clyde Schechter]

The simplest way to get this is to use the -rangestat- command, written by Robert Picard, Nick Cox, and Roberto Ferrer. -ssc install rangestat-

rangestat (sd) sd_roa = roa, interval(t -2 0) by(i)
gen raroa = roa/sd_roa

Note: As no example data was provided, this code is not tested. Beware of typos.

[Tony Annan]

Clyde, Thank you for your quick response. I have now attached an excerpt of the dataset. I will try the code, in the mean time. I appreciate!

[Clyde Schechter]

Tony, thanks for trying to help out. But attaching a spreadsheet is not the way to do it. Please read the FAQ, especially #12, for advice on the best ways to post data examples (-dataex- command) and Stata code or output (code delimiters). Spreadsheet attachments are especially discouraged because 1) some people who are frequent responders here don't use Microsoft Office products, and among those who do, 2) some are reluctant to download these documents from strangers because they are capable of harboring active malware. I, for one, never download an Excel or Word document from anybody I don't know.

Anyway, try the code. If you run into problems, post back with a -dataex- generated data example for more advice.

[Tony Annan]

Hi Clyde, After installing the Rangestat package, I run the code and got the following error messages. How do I define ROA and i in your code? Tony

. rangestat (sd) sd_roa = roa, interval(t -2 0) by(i)
variable i not found
(error in option by())
r(111);

.
. gen raroa = roa/sd_roa
roa not found
r(111);

[Tony Annan]

Sorry. I wasn't aware of the acceptable data types. I tried posting the Stata data file and it didn't work. I will figure out how dataex-generated data works and send back sample data. Tony

[Clyde Schechter]

Tony,

Two things. First, read -help rangestat- so you understand how it works. The error message you got is self-explanatory: "variable i not found (error in option by()). The -by()- option tells Stata to use a variable i, but apparently, contrary to your earlier description of your data set where you said "i is the panel id variable," you do not in fact have any variable named i. So you need to replace i by the actual name of the panel id variable. Similarly, if t is not the actual name of your date variable, you have to replace that with the name of your actual time variable. Same for roa if that variable doesn't exist. You have to tailor the code to your actual variable names. I thought I had done that for you, but it seems that your description of your data set was inaccurate.

[Tony Annan]

You're right, Clyde. It worked! My bad. Also I looked up the Dataex and run on my sample dataset (5 columns...):


MFIID MFIName FiscalYear ROA ROE
100000 "DEPROSC-Nepal" 2002 . .
100000 "DEPROSC-Nepal" 2003 -.2225 -2.3054
100000 "DEPROSC-Nepal" 2004 .0343 .1086
100000 "DEPROSC-Nepal" 2005 .0446 .1741
100000 "DEPROSC-Nepal" 2006 .0223 .1161
100000 "DEPROSC-Nepal" 2007 .0461 .3376
100000 "DEPROSC-Nepal" 2008 .0274 .2138
100000 "DEPROSC-Nepal" 2009 .0235 .1738
100000 "DEPROSC-Nepal" 2010 .0389 .3739
100001 "KredAqro NBCO" 2002 . .
100001 "KredAqro NBCO" 2003 .1182 .1187
100001 "KredAqro NBCO" 2004 .0919 .0923
100001 "KredAqro NBCO" 2005 .031 .0342
100001 "KredAqro NBCO" 2006 .0452 .0712
100001 "KredAqro NBCO" 2007 .0646 .2569
100001 "KredAqro NBCO" 2008 .0749 .4036
100001 "KredAqro NBCO" 2009 .0097 .0485
100001 "KredAqro NBCO" 2010 .012 .0492
100001 "KredAqro NBCO" 2011 .0115 .0401
100001 "KredAqro NBCO" 2012 .0625 .1742
100001 "KredAqro NBCO" 2013 .0588 .1511
100001 "KredAqro NBCO" 2014 .0583 .1655
100001 "KredAqro NBCO" 2015 -.1716 -.5654
100001 "KredAqro NBCO" 2016 -.1048 -1.1797
100003 "TEBA" 1999 . .

[Tony Annan]

Thank you so much, Clyde! It worked.