Bookmark and Share

Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down at the end of May, and its replacement, statalist.org is already up and running.


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

Re: st: reshaping database with dates ordered in wide format to long format


From   hind lazrak <hindstata@gmail.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: reshaping database with dates ordered in wide format to long format
Date   Sun, 10 Feb 2013 04:40:41 -0800

Thanks Nick for looking at this question.
I actually tried some of the code you gave not so long ago to another
fellow on the stata list (see thread with Roland on 12/7/2012) but
adapting the loop in my case was not successful.
Here's an excerpt of what my data looks like after some "rename-ing".
As you can see, the study participants have filled the question in
chronological order (it made the rename process quite easy)

list id  movedfromconsent_to3m startdateconsent PCconsent
enddateconsent startdatehome1 PChome1 enddatehome1 startdatehome2
PChome2 enddatehome2 residhx in 32/35 , sepby(id)
+----------------------------------------------------------------------------------------------------------------------------------+
id   moved~3m   startd~t   PCcons~t   enddat~t   s~ehome1   PChome1
en~ehome1   st~ehome2   PChome2   e~ehome2   residhx
----------------------------------------------------------------------------------------------------------------------------------
32.     20073          1   1-Aug-09     V6T2G1   6-Dec-09   6-Dec-09
 V6Y3W3   30-Dec-09   30-Dec-09    V6T2G1   3-Mar-10         3
33.     20073          1   1-Aug-09     V6T2G1   6-Dec-09   6-Dec-09
 V6Y3W3   30-Dec-09   30-Dec-09    V6T2G1   3-Mar-10         3
34.     20073          1   1-Aug-09     V6T2G1   6-Dec-09   6-Dec-09
 V6Y3W3   30-Dec-09   30-Dec-09    V6T2G1   3-Mar-10         3
35.     20073          1   1-Aug-09     V6T2G1   6-Dec-09   6-Dec-09
 V6Y3W3   30-Dec-09   30-Dec-09    V6T2G1   3-Mar-10         3
----------------------------------------------------------------------------------------------------------------------------------


Many thanks!

On Sun, Feb 10, 2013 at 3:34 AM, Nick Cox <njcoxstata@gmail.com> wrote:
> More strategic advice. You would need some -rename-ing first, so that
> postcodes, etc., dates, etc. end up aligned.
>
> Nick
>
> On Sun, Feb 10, 2013 at 10:48 AM, Nick Cox <njcoxstata@gmail.com> wrote:
>> I have strategic rather than tactical advice as I failed to absorb
>> everything here. -reshape long- first, however crudely, then work on
>> the dataset in that form.
>>
>> It sounds as if you are seeking a command that does everything in one
>> go, but that sounds unlikely.
>>
>> If you don't get better answers:
>>
>> 1. An example of the data usually helps.
>>
>> 2. Show us the commands you have tried so far.
>>
>> Nick
>>
>> On Sun, Feb 10, 2013 at 10:33 AM, hind lazrak <hindstata@gmail.com> wrote:
>>
>>> I have a cohort of families that are followed over time.
>>> They are asked at one time point (3months after the birth of their
>>> child) to list the addresses and dates of move in and move out where
>>> they have lived since they consented to participate in the study.
>>>
>>> For some families it is simple: they have never moved- but for others,
>>> there is quite some movement.
>>> The way the questionnaire was entered is shaped in wide format
>>> For example, consider this simplified version of the data I have
>>> For each id we have the following variables
>>> reside3mq1 reside3mq2 reside3mq2a reside3mq2b
>>> reside3mq3 reside3mq3a reside3mq3b
>>> reside3mq4 reside3mq4a reside3mq4b
>>>
>>> Data dictionary -
>>> reside3mq1 is binary and indicates if the family has moved since consent
>>>
>>> reside3mq`i' is the date of move-in  in address`i'
>>> reside3mq`i'a is the postal code at address `i'  /*postal code is alphanumeric*/
>>> reside3m`i'b is the date of move-out at address`i'
>>>
>>> So, if `i' can go from 2 to 5 (not all family move 3 times so it varies by id)
>>> How can I generate a data set where each id has `i' rows with move-in
>>> and move-out dates and corresponding postal code?
>>>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/statalist-faq/
> *   http://www.ats.ucla.edu/stat/stata/


On Sun, Feb 10, 2013 at 3:34 AM, Nick Cox <njcoxstata@gmail.com> wrote:
> More strategic advice. You would need some -rename-ing first, so that
> postcodes, etc., dates, etc. end up aligned.
>
> Nick
>
> On Sun, Feb 10, 2013 at 10:48 AM, Nick Cox <njcoxstata@gmail.com> wrote:
>> I have strategic rather than tactical advice as I failed to absorb
>> everything here. -reshape long- first, however crudely, then work on
>> the dataset in that form.
>>
>> It sounds as if you are seeking a command that does everything in one
>> go, but that sounds unlikely.
>>
>> If you don't get better answers:
>>
>> 1. An example of the data usually helps.
>>
>> 2. Show us the commands you have tried so far.
>>
>> Nick
>>
>> On Sun, Feb 10, 2013 at 10:33 AM, hind lazrak <hindstata@gmail.com> wrote:
>>
>>> I have a cohort of families that are followed over time.
>>> They are asked at one time point (3months after the birth of their
>>> child) to list the addresses and dates of move in and move out where
>>> they have lived since they consented to participate in the study.
>>>
>>> For some families it is simple: they have never moved- but for others,
>>> there is quite some movement.
>>> The way the questionnaire was entered is shaped in wide format
>>> For example, consider this simplified version of the data I have
>>> For each id we have the following variables
>>> reside3mq1 reside3mq2 reside3mq2a reside3mq2b
>>> reside3mq3 reside3mq3a reside3mq3b
>>> reside3mq4 reside3mq4a reside3mq4b
>>>
>>> Data dictionary -
>>> reside3mq1 is binary and indicates if the family has moved since consent
>>>
>>> reside3mq`i' is the date of move-in  in address`i'
>>> reside3mq`i'a is the postal code at address `i'  /*postal code is alphanumeric*/
>>> reside3m`i'b is the date of move-out at address`i'
>>>
>>> So, if `i' can go from 2 to 5 (not all family move 3 times so it varies by id)
>>> How can I generate a data set where each id has `i' rows with move-in
>>> and move-out dates and corresponding postal code?
>>>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/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/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/


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