support - neophyte stata

[Nicola Di Renzo]

Hello everybody!

Can you please help me with the following problem?

I have created an easy example - attached in the excel - that perfectly replicates what I would need to do in a larger dataset.

It is a balanced panel data (2017,2014,2010) where, unfortunately, the "ID" of the same person might change over time.
For example, ID=6 in 2017 takes the value =99 in 2010. The same happens for all IDs from 6 to 10.
On the contrary, IDs from 1 to 5 perfectly match over the three years.

I would need a code that match correctly the IDs over the different years. In other words, in the example, by sustituting 99 with 6, 98 with 7 etc.

I hope it is clear.

Thank you for your support.

Best,
Nicola

[Clyde Schechter]

Please read the Forum FAQ for excellent advice on how to post in ways that maximize your chances of getting a timely and helpful response. Among the things you will learn there:

1. Attachments of any kind are discouraged; many forum members who might otherwise answer your questions will not, for computer security reasons, download attachments from strangers. This is particularly true of Microsoft Office documents, which can contain active malware.
2. Example data should always be from a Stata data set, not from other software. If you have not yet imported this data into Stata, it is premature to ask questions about how to work with it, as the details of how it is organized in Stata are often critical to the correct solution. If you do notknow how to import your spreadsheet into Stata, see -help import excel-.
3. The most useful way to show example data from a Stata data set is with the -dataex- command. If you are running version 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.

Please post back with example data shown using -dataex- so that somebody can help you out.

[William Lisowski]

I agree with all the Clyde said about the preferred presentation of questions on Statalist. Do please yskr a few moments to review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It is particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using code delimiters [CODE] and [/CODE], and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

One question your example data does not address: for a given individual can the ID change more than once across the course of the data? Could you have

* Example generated by -dataex-. For more info, type help dataex
clear
input int (id past_id wave)
10 55 2017
55 95 2014
95 95 2010
end

[Nicola Di Renzo]

Dear Clyde, William,

Thank you very much for your prompt feedback, I'll pay more attention in the future.

@william: Yes indeed it could be the case, I haven't shown you this possibility because at the beginning I imposed id=past_id for 2017

I would try the following (from a logical perspective):
if year=2014 & id!=past id;
then "vlookup" past_id(2014) into id 2010
when found, substitute id(2010) with corresponding to id(2014)

I suppose there exists more efficent ways to solve it.

Thanks again

[Nicola Di Renzo]

* Example generated by -dataex-. To install: ssc install dataex
from dataex, as suggested

clear
input int (id past_id wave)
 1  1 2017
 2  2 2017
 3  3 2017
 4  4 2017
 5  5 2017
 6  6 2017
 7  7 2017
 8  8 2017
 9  9 2017
10 10 2017
 1  1 2014
 2  2 2014
 3  3 2014
 4  4 2014
 5  5 2014
 6 99 2014
 7 98 2014
 8 97 2014
 9 96 2014
10 95 2014
 1  1 2010
 2  2 2010
 3  3 2010
 4  4 2010
 5  5 2010
99 99 2010
98 98 2010
97 97 2010
96 96 2010
95 95 2010
end

[Andrew Musau]

group_id from SSC, by Robert Picard, was designed for these kinds of problems.

ssc install group_id, replace

In the future, please consider using more informative titles as this will help others when faced with the same problem.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(id past_id wave)
 1  1 2017
 2  2 2017
 3  3 2017
 4  4 2017
 5  5 2017
 6  6 2017
 7  7 2017
 8  8 2017
 9  9 2017
10 10 2017
 1  1 2014
 2  2 2014
 3  3 2014
 4  4 2014
 5  5 2014
 6 99 2014
 7 98 2014
 8 97 2014
 9 96 2014
10 95 2014
 1  1 2010
 2  2 2010
 3  3 2010
 4  4 2010
 5  5 2010
99 99 2010
98 98 2010
97 97 2010
96 96 2010
95 95 2010
end

clonevar newid= id
group_id newid, matchby(past_id)

Res.:

. sort newid wave

. l, sepby(newid)

     +-----------------------------+
     | id   past_id   wave   newid |
     |-----------------------------|
  1. |  1         1   2010       1 |
  2. |  1         1   2014       1 |
  3. |  1         1   2017       1 |
     |-----------------------------|
  4. |  2         2   2010       2 |
  5. |  2         2   2014       2 |
  6. |  2         2   2017       2 |
     |-----------------------------|
  7. |  3         3   2010       3 |
  8. |  3         3   2014       3 |
  9. |  3         3   2017       3 |
     |-----------------------------|
 10. |  4         4   2010       4 |
 11. |  4         4   2014       4 |
 12. |  4         4   2017       4 |
     |-----------------------------|
 13. |  5         5   2010       5 |
 14. |  5         5   2014       5 |
 15. |  5         5   2017       5 |
     |-----------------------------|
 16. | 99        99   2010       6 |
 17. |  6        99   2014       6 |
 18. |  6         6   2017       6 |
     |-----------------------------|
 19. | 98        98   2010       7 |
 20. |  7        98   2014       7 |
 21. |  7         7   2017       7 |
     |-----------------------------|
 22. | 97        97   2010       8 |
 23. |  8        97   2014       8 |
 24. |  8         8   2017       8 |
     |-----------------------------|
 25. | 96        96   2010       9 |
 26. |  9        96   2014       9 |
 27. |  9         9   2017       9 |
     |-----------------------------|
 28. | 95        95   2010      10 |
 29. | 10        95   2014      10 |
 30. | 10        10   2017      10 |
     +-----------------------------+

.

[Nicola Di Renzo]

Thank you very much Andrew!!!