Thank you, Jeph. Your suggestion worked with a small modification. I had
to replace the first month's period to 1 (it came up as 0) and the 7th
month's period to 2 which I achieved by executing the following
commands:
replace period=1 if period==0
bys id (month): gen prbmyr=month[7]
replace period=2 if month==prbmyr
Thank you again!
Josiane "Josie" Georges
Sr. Planning Analyst
Division of Mental Hygiene
Tel: 212-219-5194
Fax: 212-219-5192
Email: [email protected]
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Jeph Herrin
Sent: Wednesday, May 27, 2009 2:18 PM
To: [email protected]
Subject: Re: st: Dataset transformation problem
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/
*
* 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/
