Statalist


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: st: Dataset transformation problem


From   Austin Nichols <austinnichols@gmail.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: Dataset transformation problem
Date   Wed, 27 May 2009 13:07:28 -0400

Josiane Georges <jgeorges@health.nyc.gov>:

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 <kantor.d@att.net> 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/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index