group data in a panel set

[Jessie Lu]

Dear Statalist,

Here is a part of the sample data, which shows the gender of each employee in 3500 companies in 20 years.

code	year	gender	age
1	2000	female	46
1	2000	male	55
1	2000	male	47
1	2001	male	55
1	2001	female	32
1	2001	male	54
1	2002	female	65
1	2002	female	23
1	2002	female	54
2	2000	male	65
2	2000	male	76
2	2000	male	34
2	2001	female	56
2	2001	male	75

Is any way that I could convert the above data set to a panel that directly shows the total number of female employees in each company in different years? After sort the female out, how could I calculate the average age of all female employee in each company in different years?

code	year	female	averagefemaleage
01	2000	1	46
01	2001	1	32
01	2002	3	47
02	2000	0	..
02	2001	1	..
02	2002	..
03	2000	..
03	2001	..
03	2002	..

[Øyvind Snilsberg]

gen female = gender == "female"
collapse (sum) female ,by(code year)

[Jessie Lu]

gen female = gender == "female"
collapse (sum) female ,by(code year)

Thank you! It was really helpful. What if I want to calculate the average of their age and convert to Panel? Is it just change (sum) to (average)?

[Øyvind Snilsberg]

yes, to obtain the average age of men and women,

collapse (mean) age ,by(code gender year)

edit: if you want the number of female employees and their average age in the same panel,

gen female = gender == "female"
gen female_age = age if female
collapse (sum) female (mean) female_age ,by(code year)