column operations

[Abel Csatho]

Dear People,

I have time-series data for settlements, and I would need the differences between the values of two different years.
It means that I should distract the values of row(n-4) from row(n).
Could somebody please help how can I do that?

Thank you,
Ábel

[Clyde Schechter]

You don't show example data, but assuming you have data in long layout with a variable settlement, that identifies settlements, and a variable, year, that identifies years, and the variable you want to difference over 4 years is called x, you can do that with:

by settlement (year), sort: gen wanted = x - x[_n-4]

Caveat: if your data has gaps, be aware that this code will subtract the value of x four observations ("rows") up--which may not be the same as 4 years earlier. If you really intend to subtract the value four years earlier, then a safer way to do this is:

xtset settlement year
gen wanted = x - L4.x

This approach will actually find the value four years earlier (assuming it exists) for the subtraction, even if it is only 1, 2, or 3 observations higher up in the data.

If these codes don't work in your data, it may well be because I have had to rely on my imagination about what your data is like, or you have not clearly explained your problem. If further help is needed, when posting back, be sure to show example data, and use the -dataex- command to do so. If you are running version 18, 17, 16 or a fully updated version 15.1 or 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- to 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.

In addition to showing example data, to be completely clear about what you want, it might be best to pick out a very small subset of the data, hand-calculate what you want, and show that calculation and its results.