Taking the difference of observations of the same variable

[Jean Moulin]

Dear all,

My problem is the following: I have a panel data of EU countries in which I have many kinds of variables. One of them is the sovereign interest rates (i hereafter). I would like to compute the spread between any EU countries i's and Germany's one for each year. For instance, Italian-German spread in 2010 (i_italy-2010 - i_germany_2010). What is confusing to me is that I wouldn't make the difference between two variables, but between two observations of the same variable (the variable i). Does anyone has an idea about how to solve this issue ? I suppose I would have to start by bys country year. Thanks a lot.

Jean

[William Lisowski]

This is simple to accomplish, but the details depend very much on the organization of your data. I will expand on advice you were given by Nick Cox in the previous topic you started.

Even the best descriptions of data are no substitute for an actual example of the data. There are many ways your data might be organized that are consistent with your description, and each would require a somewhat different approach. In order to get a helpful response, you need to show some example data.

Be sure to use the dataex command to do this. If you are running version 15.1 or later, or a fully updated version 14.2, dataex is already part of your official Stata installation. If not, run ssc install dataex to get it. Either way, run help dataex and read the simple instructions for using it. dataex will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use dataex.

In your case, let us imagine you the variables c, y, and i for the country, year, and sovereign interest rate. A useful subset of your data would be

dataex c y i if inlist(c,"Germany","France","Spain") & inlist(y,2010,2011,2012)

[Jean Moulin]

Thank you for your answer and advice, this is indeed very useful. On the basis of your suggestion, please find below a subset of my dataset:

"France" 2010 3.12
"France" 2011 3.32
"France" 2012 2.54
"Germany" 2010 2.74
"Germany" 2011 2.61
"Germany" 2012 1.5
"Spain" 2010 4.25
"Spain" 2011 5.44
"Spain" 2012 5.85

The third column corresponds to the nominal interest from which I have to take the difference with Germany's one for each country and year. I hope it's clearer now. Thank a lot.

Jean

[William Lisowski]

I think this does what you want.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 c int y float i
"France"  2010 3.12
"France"  2011 3.32
"France"  2012 2.54
"Germany" 2010 2.74
"Germany" 2011 2.61
"Germany" 2012  1.5
"Spain"   2010 4.25
"Spain"   2011 5.44
"Spain"   2012 5.85
end

generate gfirst = 2
replace gfirst = 1 if c=="Germany"
sort y gfirst
by y: generate spread = i-i[1]
format %9.2f spread
drop gfirst
sort c y

. list, sepby(c) noobs

  +--------------------------------+
  |       c      y      i   spread |
  |--------------------------------|
  |  France   2010   3.12     0.38 |
  |  France   2011   3.32     0.71 |
  |  France   2012   2.54     1.04 |
  |--------------------------------|
  | Germany   2010   2.74     0.00 |
  | Germany   2011   2.61     0.00 |
  | Germany   2012    1.5     0.00 |
  |--------------------------------|
  |   Spain   2010   4.25     1.51 |
  |   Spain   2011   5.44     2.83 |
  |   Spain   2012   5.85     4.35 |
  +--------------------------------+

[Jean Moulin]

That's working, thanks a lot.