Choose Language Hide Translation Bar
Highlighted
Luq2
Level I

Transforming multiple single choice column into multi choice column-sets

Hello,

 

I have the following data arrangement:

 

Three columns with codes and labels which have the same contents, e.g. 1=Washington, 2=New York, 3=San Francisco,...

Washington could be chosen in two or three columns.

 

I now want to rearrange it as it would be a multi code variable: One column for Washington, one for New York etc, each with codes 1 (for selected) and 0 (for not selected).

 

Is there any easy solution to this?

 

Thank you very much.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Transforming multiple single choice column into multi choice column-sets

This is not very complex, but it takes a few steps to do this interactively

  1. Assuming your data looks like this:choice1.PNG 
  2. Stack the data by going to         Tables==>Stack       and placing into the Stack Column selection box, your 3 Choice columns.  It will create a data table that looks like:choice2.PNG
  3. The Label column needs to have all of its values changed to 1, so select the Label column, click on the red triangle in the column panel, and select Recode choice3.PNG
  4. Change all 3 values of Choice 1, Choice 2 and Choice 3 to have the value 1 and  click Recode.  You will get a data table that looks like                :choice4.PNG
  5. Now the table needs to be Split back into columns, so go to         Tables==>Split and set up the dialog box as shown below              choice5.PNG
  6. Here is the resulting table:                  choice6.PNG
  7. Now the blanks need to be filled in with zeros "0".  To do this, a simple cut and paste can be used.  The first thing is to go to any one of the blank cells and type in the number 0.
  8. Copy the contents of that cell into the paste buffer by selecting it, and using CNTL/C to copy it.
  9. Now go to the Washington column and right click on any one of blank cells, and select, Select Matching Cells.
  10. While keeping the cursor in the column use CNTL/V to paste the saved "0" into all of the selected cells.
  11. Repeat the above 2 steps(9,10) for the New York and San Francisco columns.
  12. Here is the final table            choice7.PNG
Jim

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

Re: Transforming multiple single choice column into multi choice column-sets

This is not very complex, but it takes a few steps to do this interactively

  1. Assuming your data looks like this:choice1.PNG 
  2. Stack the data by going to         Tables==>Stack       and placing into the Stack Column selection box, your 3 Choice columns.  It will create a data table that looks like:choice2.PNG
  3. The Label column needs to have all of its values changed to 1, so select the Label column, click on the red triangle in the column panel, and select Recode choice3.PNG
  4. Change all 3 values of Choice 1, Choice 2 and Choice 3 to have the value 1 and  click Recode.  You will get a data table that looks like                :choice4.PNG
  5. Now the table needs to be Split back into columns, so go to         Tables==>Split and set up the dialog box as shown below              choice5.PNG
  6. Here is the resulting table:                  choice6.PNG
  7. Now the blanks need to be filled in with zeros "0".  To do this, a simple cut and paste can be used.  The first thing is to go to any one of the blank cells and type in the number 0.
  8. Copy the contents of that cell into the paste buffer by selecting it, and using CNTL/C to copy it.
  9. Now go to the Washington column and right click on any one of blank cells, and select, Select Matching Cells.
  10. While keeping the cursor in the column use CNTL/V to paste the saved "0" into all of the selected cells.
  11. Repeat the above 2 steps(9,10) for the New York and San Francisco columns.
  12. Here is the final table            choice7.PNG
Jim

View solution in original post

Highlighted
Luq2
Level I

Re: Transforming multiple single choice column into multi choice column-sets

Wo, thank you very much for the quick response and you effort.

 

This solves the problem. I tried stacking and splitting, but did not get the result. With this approach (esp. step 3 and 4), it works perfectly.

 

Thanks

Article Labels

    There are no labels assigned to this post.