# RE: st: Dataset transformation problem

 From "Josiane Georges" To Subject RE: st: Dataset transformation problem Date Wed, 27 May 2009 17:52:01 -0400

```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: jgeorges@health.nyc.gov

-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Jeph Herrin
Sent: Wednesday, May 27, 2009 2:18 PM
To: statalist@hsphsun2.harvard.edu
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: jgeorges@health.nyc.gov
>
>
> *
> *   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/
```