Summarize variable by ID and year

[Thomas Dudek]

Hello everyone,

I have a panel dataset of individuals (children). There, I have the individual's and their mother's ID and mother's income. I also have their birth years, the survey year, and much more.
What I would like to do is create a variable that equals 1 (or the income) for each child only in the first (lowest) year observed within the datapanel.
I need the mother's income at the time the household was surveyed the first time and tried something like:

sort child_id year
gen mother_pay = mother_pay_dv if min(year)==1

Then I tried something like:
bysort child_id mother_pay: egen mother_pay = mother_pay_dv if min(year)==1

I thought Stata would give me the pay of the mother for the first (minimum) year the household was observed but this doesn't do what I need. I'm also not using the syntax correctly but don't understand the help so well when it gets a bit more technical :-/


Any suggestions, please?


Here is a data example:

birthy	age	year	child_id	mother_id	mother_pay_dv
1997	0	1997	1400962	5285651	18013.86
1997	1	1998	1400962	5285651	28221.71
1997	2	1999	1400962	5285651
1997	3	2000	1400962	5285651	32424.94
1997	4	2001	1400962	5285651	65000
1997	5	2002	1400962	5285651	91000
1997	6	2003	1400962	5285651

Thank you!

[Clyde Schechter]

I think this does what you want:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int birthy byte age int year long(child_id mother_id) float mother_pay_dv
1997 0 1997 1400962 5285651 18013.86
1997 1 1998 1400962 5285651 28221.71
1997 2 1999 1400962 5285651        .
1997 3 2000 1400962 5285651 32424.94
1997 4 2001 1400962 5285651    65000
1997 5 2002 1400962 5285651    91000
1997 6 2003 1400962 5285651        .
end

by mother_id (year), sort: gen mother_pay_first_year = mother_pay_dv[1]

In the future, when showing data examples, please use the -dataex- command to do so, as I have in this response. If you are running version 15.1 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- 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-.

[Thomas Dudek]

Hi!

Thanks so much for answering this so quickly and efficiently. Sorry about posting my data example so badly, I'll know better for my next question.
The code does exactly what I needed!

Many thanks,
Thomas