Something like:
gen month=mofd(date)
bys id (date): gen firstmonth=month[1]
gen period=ceil((month-firstmonth)/6)
collapse (sum) unit , by(id period)
reshape wide period, i(id)
?
hth,
Jeph
Josiane Georges wrote:
Hello Fellow Stata Listserv Subscribers:
I have a dataset transformation problem that I could use some guidance
with. I want to collapse a large dataset structured like the example
dataset below to list one observation for each id containing total units
in each 6 month period. I need the dataset to only generate sums for
consecutive months within discreet 6 month periods only. Please note
that each id has a different first month and has observations for a
different set of months.
For example for id 001 it should generate the following observation:
DESIRED RESULT
id period1 period2 period3
001 26.4 14.1 17.8
EXAMPLE DATASET
id date units month
001 01jan2003 3.5 516
001 01feb2003 8.1 517
001 01mar2003 9.0 518
001 01apr2003 5.8 519
001 01may2003 5.5 540
001 01jun2003 8.6 541
001 01jul2003 8.9 552
001 01aug2003 8.9 553
004 01jan2003 4.6 520
004 01feb2003 4.1 521
004 01mar2003 4.6 522
004 01apr2003 4.4 523
004 01may2003 4.6 530
004 01jun2003 4.4 532
004 01jul2003 4.6 540
Josiane "Josie" Georges
Sr. Planning Analyst
Office of Planning
Division of Mental Hygiene
93 Worth Street, Rm 611
New York, NY 10013
Tel: 212-219-5194
Fax: 212-219-5192
Email: [email protected]
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
