Generating variable that measures the difference between the last and first year

[Pete Willemsen]

Dear Statalisters,

I am trying to generate a variable that measures how much the CPIA index for institutional quality has de- or increased per country. For some countries data is available for the entire time-frame of my research (2008-2016). However, for some countries the CPIA index is only available for, for example, 2011 and 2012 (the rest is missing). For all countries I would like to know the difference between the value for the last and first year of which data is available.

For answering this question, there are the following variables of interest:

Country
Year
CPIA

I hope my question is clear enough and someone can help me with answering.

[Clyde Schechter]

//    CREATE DEMONSTRATION DATA SET
clear*
set obs 90
gen country = ceil(_n/9)
by country, sort: gen year = 2007 + _n
set seed 1234
gen cpia = rnormal(50, 10)
replace cpia = . if runiform() < 0.4

// THE CODE STARTS HERE
gen byte has_cpia = 1 if !missing(cpia)
by country (has_cpia year), sort: gen first_cpia = cpia[1]
replace has_cpia = 0 if missing(cpia)
by country (has_cpia year), sort: gen last_cpia = cpia[_N]
gen cpia_change = last_cpia - first_cpia

In the future, when asking for help with code, please post example data, and use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, it 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.



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

[Pete Willemsen]

Thank you a lot for your help, it worked!
In future posts I will listen to your advise, thanks for that as well!