st: reshape query

[Tim Evans <Tim.Evans@phe.gov.uk>]

Hi all,


I'm using Stata 11.2 and trying to reshape a dataset to an output that I would like.

I am trying to get my data in the format (note that invsurg2 is spurious at the moment and will be dropped when I get to this point)

age	y	n
17	1	0
18	1	0
19	2	0
20	2	0
21	1	0
22	3	0
23	3	1
24	9	0
25	12	5
~~
~~
94	22	108
95	9	87
96	12	65
97	2	44
98	4	16
99	1	11

y and n relate to invsurg2 which is shown below

My initial data look like this:

age	invsurg2	id	age_
17	Y		1		1
18	Y		1		1
19	Y		1		2
20	Y		1		2
21	Y		1		1
22	Y		1		3
23	Y		1		3
24	Y		1		9
25	Y		1		12
26	Y		1		19
27	Y		1		26
28	Y		1		32
29	Y		1		41
30	Y		1		65
31	Y		1		86
32	Y		1		75

Where invsurg2 is a Y/N variable (numeric behind a label), id has been generated using:
bysort age: g id=_n
and age_ currently relates to a count, but for reasons of reshaping, found it helpful to call the variable age_

This run of data runs to age 99, and then is repeated, but for invsurg2==N

I reshape the data using the following:

reshape wide age_, i(id inv) j(age)

Which returns the following:

id	invsurg2	age_17	age_18	age_19	age_20	age_21	age_22	age_23	age_24	age_25	age_94	age_95	age_96	age_97	age_98	age_99
1	Y		1	1	2	2	1	3	3	9	12	22	9	12	2	4	1
2	N		0	0	0	0	0	0	1	0	5	108	87	65	44	16	11

What I have been struggling with is how to complete my final reshape to provide this desired output:

age	y	n
17	1	0
18	1	0
19	2	0
20	2	0

As indicated earlier, invsurg2

I would appreciate any help here.

Best wishes

Tim

**************************************************************************
The information contained in the EMail and any attachments is confidential and intended solely and for the attention and use of the named addressee(s). It may not be disclosed to any other person without the express authority of Public Health England, or the intended recipient, or both. If you are not the intended recipient, you must not disclose, copy, distribute or retain this message or any part of it. This footnote also confirms that this EMail has been swept for computer viruses by Symantec.Cloud, but please re-sweep any attachments before opening or saving. http://www.gov.uk/PHE
**************************************************************************

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/