how get a new panel data?

[Xion Larqui]

Dear friends,
I would like to ask you for helps, please. I have a data panel and I want to get aggregate data as new database. To get the new database I will use median so how it be able to run stata code for this issue?
An idea of my data panel is like that:

id	year	wage	zone
1	1980	500	a
2	1980	100	b
3	1980	200	c
4	1980	4000	d
5	1980	3000	a
6	1980	7500	b
7	1980	800	c
8	1980	900	d
9	1980	2500	a
10	1980	11000	c
1	1981	800	a
2	1981	450	b
3	1981	700	c
4	1981	4200	d
5	1981	3100	a
6	1981	7600	b
7	1981	1050	c
8	1981	1250	d
9	1981	2550	a
10	1981	11550	c
1	1982	1020	a
2	1982	720	b
3	1982	850	c
4	1982	4280	d
5	1982	3235	a
6	1982	7733	b
7	1982	1328	c
8	1982	1494	d
9	1982	2962	a
10	1982	11588	c
1	1983	1208	a
2	1983	1066	b
3	1983	1461	c
4	1983	4821	d
5	1983	3605	a
6	1983	7967	b
7	1983	2015	c
8	1983	2436	d
9	1983	3744	a
10	1983	11775	c

And new panel data could be as:

zone	year	median	n
a	1980	2500	3
a	1981	2550	3
a	1982	2962	3
a	1983	3605	3
b	1980	3800	2
b	1981	4025	2
b	1982	4226,5	2
b	1983	4516,5	2
c	1980	800	3
c	1981	1050	3
c	1982	1328	3
c	1983	2015	3
d	1980	2450	2
d	1981	2725	2
d	1982	2887	2
d	1983	3628,5	2

I hope that somebody will be able to help, please.
Thank so much,

Xion L.

[William Lisowski]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id int(year wage) str1 zone
 1 1980   500 "a"
 2 1980   100 "b"
 3 1980   200 "c"
 4 1980  4000 "d"
 5 1980  3000 "a"
 6 1980  7500 "b"
 7 1980   800 "c"
 8 1980   900 "d"
 9 1980  2500 "a"
10 1980 11000 "c"
 1 1981   800 "a"
 2 1981   450 "b"
 3 1981   700 "c"
 4 1981  4200 "d"
 5 1981  3100 "a"
 6 1981  7600 "b"
 7 1981  1050 "c"
 8 1981  1250 "d"
 9 1981  2550 "a"
10 1981 11550 "c"
 1 1982  1020 "a"
 2 1982   720 "b"
 3 1982   850 "c"
 4 1982  4280 "d"
 5 1982  3235 "a"
 6 1982  7733 "b"
 7 1982  1328 "c"
 8 1982  1494 "d"
 9 1982  2962 "a"
10 1982 11588 "c"
 1 1983  1208 "a"
 2 1983  1066 "b"
 3 1983  1461 "c"
 4 1983  4821 "d"
 5 1983  3605 "a"
 6 1983  7967 "b"
 7 1983  2015 "c"
 8 1983  2436 "d"
 9 1983  3744 "a"
10 1983 11775 "c"
end
collapse (median) wage (count) n=wage, by(zone year)
list, noobs sepby(zone)

 list, noobs sepby(zone)

  +--------------------------+
  | year   zone     wage   n |
  |--------------------------|
  | 1980      a     2500   3 |
  | 1981      a     2550   3 |
  | 1982      a     2962   3 |
  | 1983      a     3605   3 |
  |--------------------------|
  | 1980      b     3800   2 |
  | 1981      b     4025   2 |
  | 1982      b   4226.5   2 |
  | 1983      b   4516.5   2 |
  |--------------------------|
  | 1980      c      800   3 |
  | 1981      c     1050   3 |
  | 1982      c     1328   3 |
  | 1983      c     2015   3 |
  |--------------------------|
  | 1980      d     2450   2 |
  | 1981      d     2725   2 |
  | 1982      d     2887   2 |
  | 1983      d   3628.5   2 |
  +--------------------------+

[Xion Larqui]

Dear William,

Thank you a lot, for very soon answer. I will run the code... with a big database...
Thank you again, and my best wishes
Xion L.


. import excel "C:\file2.xlsx", sheet("s1") firstrow clear
(4 vars, 40 obs)

. collapse (median) wage (count) n=wage, by(zone year)

. list, noobs sepby(zone)

+--------------------------+
| year zone wage n |
|--------------------------|
| 1980 a 2500 3 |
| 1981 a 2550 3 |
| 1982 a 2962 3 |
| 1983 a 3605 3 |
|--------------------------|
| 1980 b 3800 2 |
| 1981 b 4025 2 |
| 1982 b 4226.5 2 |
| 1983 b 4516.5 2 |
|--------------------------|
| 1980 c 800 3 |
| 1981 c 1050 3 |
| 1982 c 1328 3 |
| 1983 c 2015 3 |
|--------------------------|
| 1980 d 2450 2 |
| 1981 d 2725 2 |
| 1982 d 2887 2 |
| 1983 d 3628.5 2 |
+--------------------------+