# RE: st: Dataset transformation problem

 From "Josiane Georges" To Subject RE: st: Dataset transformation problem Date Wed, 27 May 2009 13:16:44 -0400

```Hi David,

The periods are based on month, you can ignore the date.

The unit total periods should include only values within 6 months of the
starting month, whether or not there is a gap. To use the example you
have provided below the values for months the value of months 516, 517,
518, 519, 521 would be added together regardless of the fact that the
value 520 is missing. Since month 521 is within 6 months of the
beginning month, which in this case is 516, the values would be added
together.

So if a value is observed beyond 6 months from the earliest month in the
period, it will not be added with it. I will belong to the next period.

-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of David Kantor
Sent: Wednesday, May 27, 2009 12:51 PM
To: statalist@hsphsun2.harvard.edu
Subject: Re: st: Dataset transformation problem

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/

*
*   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/
```