Re: st: Fwd: Automation: moving values to eliminate blank cells from variables.

[Nick Cox <njcoxstata@gmail.com>]

No looping should be needed or attempted here. The composite string
variable doesn't help much, as you only need to take it apart again.
In a sentence, you need a double -reshape- with reordering in between.

This may help:

. l

     +------------------------+
     | X1   X2   X3   X4   X5 |
     |------------------------|
  1. |  1    .    .    2    . |
  2. |  1    2    3    .    . |
  3. |  1    2    .    3    4 |
  4. |  .    1    .    2    . |
     +------------------------+

. gen id = _n

. reshape long X, i(id)

. drop if missing(X)

. bysort id (_j) : gen j = _n

. drop _j

. reshape wide X , i(id) j(j)

. l

     +------------------------+
     | id   X1   X2   X3   X4 |
     |------------------------|
  1. |  1    1    2    .    . |
  2. |  2    1    2    3    . |
  3. |  3    1    2    3    4 |
  4. |  4    1    2    .    . |
     +------------------------+


Nick
njcoxstata@gmail.com


On 24 June 2013 16:27, John Adam Roberts <roberts.john.adam@gmail.com> wrote:
>  Specifically, the form allowed for up to 15 organizations to be entered into
>  a particular set of fields, but many forms were not filled out in order and
>  I need to correct for this.
>
>  Example of what I have:
>   (X1-X6 are the variables, and the numbers below represent where the
>  observations are found.  e.g. in row 1, 2 is under the X4.  This refers to
>  the fact that the second observation is listed under variable X4, when it
>  should be under X2.)
>
>  fig. 1
>  X1    X2    X3    X4    X5    X6
>   1                     2
>   1      2      3
>   1      2             3      4
>           1             2
>
>  Example of what I need to achieve:
>  fig. 2
>  X1    X2    X3    X4    X5    X6
>   1      2
>   1      2      3
>   1      2      3      4
>   1      2
>
>
>  I have built a string variable Y that holds where the information is.
>  e.g.
>  for row 1 fig.1, it is    "1, , ,2, , "
>  for row 2 fig.1, it is    "1,2,3, , , "
>  for row 3 fig.1, it is    "1,2, ,3,4, "
>
>  Now I need to implement a loop to actually move the values from the cells
>  they are in, to where they should be.
>  This is the code structure that I have been trying to implement.
>>
>> foreach _n in _N{
>>         **sets macro to the relevant cell of Y, and figures out how many
>> values I have in the
>>         **macro
>> local posY = Y[_n]
>> local n_models: word count "`posY'"
>> foreach z in "`posY'"{
>>
>> ** replace cell with the contents of the variable it should have (as
>> identified in Y)
>>   forval i = 1/"`n_models'" {
>> local y: word `i' of "`posY'"
>> replace Bidder_`i' = Bidder_`y'
>> }
>> }
>>
>> ** make all remaining variables blank
>>         forval i = "`n_model'"/15 {
>> replace Bidder_`i' = ""
>> }
>>
>> }
>
>
>  Any help on how to make this work, or even a different approach to the
>  problem would be greatly appreciated.
*
*   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/