Josiane Georges <[email protected]>:
I agree clarification is required--but try out these examples, for ideas:
clear
input id str9 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
end
g d=date(date,"DMY")
g ym=ym(year(d),month(d))
format ym %tm
tsset id ym
ren units u
g s=u+l.u+l2.u+l3.u+l4.u+l5.u
list, sepby(id)
keep if s<.
drop date d u month
reshape wide s, i(id) j(ym)
list
clear
input id str9 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
end
tsset id month
ren units u
g s=u+max(0,l.u)+max(0,l2.u)+max(0,l3.u)+max(0,l4.u)+max(0,l5.u)
list, sepby(id)
keep if s<.
drop date u
reshape wide s, i(id) j(month)
list
On Wed, May 27, 2009 at 12:50 PM, David Kantor <[email protected]> wrote:
> 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 discrete 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
*
* 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/
