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

[John Adam Roberts <roberts.john.adam@gmail.com>]

Thank you Nick, that is quite helpful; I never thought about solving
it that way.



On Mon, Jun 24, 2013 at 11:44 AM, Nick Cox <njcoxstata@gmail.com> wrote:
> 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/



-- 
Sincerely,
(John) Adam Roberts
*
*   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/