Re: st: Re: Combining multiple observations into one observation with multiple variables

[Conor Hughes <cbhughes@uchicago.edu>]

Hi Sven,
Thanks for your reply, that did the trick perfectly.  I'd never heard
of the --reshape-- command before.

Thanks again,
Conor

2010/6/30 Sven-Oliver Spieß <svenoliverspiess@gmail.com>:
> Hi Conor
>
>
> Generally 'reshape' would do that. Does the following example point in
> the right direction?
>
> ===================================
> input id char
> 1 1
> 1 3
> 1 7
> 1 11
> 2 1
> 2 8
> 3 2
> 3 7
> 3 13
> end
>
> bysort id (char): gen count = _n
> reshape wide char, i(id) j(count)
> list
> ===================================
>
>
>
> Best,
> Sven-Oliver
>
>
> On Wed, Jun 30, 2010 at 09:06, Conor Hughes <cbhughes@uchicago.edu> wrote:
>> Sorry, my tables got smushed:
>> Dataset1
>> ----------------------------------------
>> household id | individual id
>> ----------------------------------------
>>         1        |        1
>>         1        |        2
>>         1        |        3
>>         2        |        1
>>         2        |        2
>>         3        |        1
>>         3        |        2
>>
>> Dataset 2
>> -----------------------------------------------------------
>> household id | household characteristic id
>> ------------------------------------------------------------
>>         1        |                 1
>>         1        |                 3
>>         1        |                 7
>>         1        |                11
>>         2        |                 1
>>         2        |                 8
>>         3        |                 2
>>         3        |                 7
>>         3        |                13
>>
>>
>> On Wed, Jun 30, 2010 at 1:40 PM, Conor Hughes <cbhughes@uchicago.edu> wrote:
>>> Hi All,
>>> I have a couple of survey datasets that I need to merge, but they're
>>> organized in an inconvenient way.  The first is organized by
>>> household, and individuals within the household.  The second is only
>>> organized by household.  I'd like to do a many-to-one merge on
>>> household, so as to preserve the individual id's.  However, in the
>>> second dataset, rather than adding household characteristics as
>>> variables, it adds them as observations, e.g.:
>>>
>>> Dataset 1                                                          Dataset 2
>>> -------------------------------------
>>> -----------------------------------------------------------
>>> household id | individual id                        household id |
>>> household characteristic id
>>> -------------------------------------
>>> ------------------------------------------------------------
>>>          1        |        1
>>> 1        |            1
>>>          1        |        2
>>> 1        |            3
>>>          1        |        3
>>> 1        |            7
>>>          2        |        1
>>> 1        |            11
>>>          2        |        2
>>> 2        |             1
>>>          3        |        1
>>> 2        |             8
>>>          3        |        2
>>> 3        |             2
>>>
>>> 3        |             7
>>>
>>> 3        |             13
>>> I'd prefer, in the second dataset, to have one observation for each
>>> household, including household characteristics as dummy variables.  As
>>> it is, the only way to get them together is via many-to-many merge,
>>> which is foolish and doesn't work well, giving an output like
>>> -------------------------------------------------------------------------------
>>> household id | individual id | household characteristic id
>>> -------------------------------------------------------------------------------
>>>          1        |        1         |            1
>>>          1        |        2         |            3
>>>          1        |        3         |            7
>>>          1        |        3         |            11
>>>          2        |        1         |             1
>>>          2        |        2         |             8
>>>          3        |        1         |             2
>>>          3        |        2         |             7
>>>          3        |        2         |            13
>>>    Which messes up the the first dataset, since it creates repeat
>>> observations of individuals.  Is there a graceful way of the changing
>>> the multiple observations per household in the second dataset to one
>>> observation per household with characteristics represented as dummy
>>> variables?  Any help would be greatly appreciated.  And please let me
>>> know if I've described the situation poorly and you'd like
>>> clarification.
>>>
>>> Cheers,
>>> Conor
>>>
>>
>>
>>
>> --
>> Conor Hughes
>> Mathematics and Economics
>> University of Chicago 2011
>>
>> *
>> *   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/
>



-- 
Conor Hughes
Mathematics and Economics
University of Chicago 2011

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