# Re: st: Dataset transformation problem

 From David Kantor To statalist@hsphsun2.harvard.edu Subject Re: st: Dataset transformation problem Date Wed, 27 May 2009 12:50:36 -0400

You need to clarify how the periods are to be determined. They seem to be based on month -- not date. And you seem to want to group them into consecutive spells. But where does 6-month periods come in?
Do you want to further break up spells if they exceed 6 months?
What if there is a gap in month, but the sequence remains within a span of 6? E.g., 516, 517, 518, 519, 521; is 521 the start of a new period?
Once these matters are clarified, the rest is easy.

--David

At 12:19 PM 5/27/2009, you 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/