splitting up a column with ";" delimiter

[Nancy Rusinak-2]

Hello,

I have inherited a data set that with a column called "Class_Type" with that contains values split by a semicolon.  I'd like to put each item into its own column.  Here is a sample of 3 rows of data:

row 1:  agriculture; farming; finance; art
row 2:  education
row 3:  art; education; farming; finance; math; science

Some rows have one value; others have more.  There are 16 possible values that can be entered into each field.  Therefore, I'm interested in creating 16 separate columns.  We can call them "Class 1" through "Class 16."  With the data above, row 1 would have "agriculture" in the "Class 1" column; "farming" in the "Class 2" column and so forth.  The fields "Class 5" through "Class 16" would be blank for that row.  

Basically, I think I need some syntax that looks for the ";" to act as a stop but I'm not familiar with how to do that.

Again, I inherited this.  No need to berate me for sloppy set up.  Thanks for the help, all.  Really appreciate it.

Nancy

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Jon Peck]

This is exactly the same as another recent post to this list except with "," and a different number of possible values.  Here is what I posted earlier with these mods.  Some other solutions were also posted.

If x is a variable holding a list of ;-separated numeric values, this code will produce separate variables with one value each.

spssinc trans result=z1 to z16

/formula "re.split(';', x)".

z1 to z16 are the variables to be produced.  Change the upper limit (z16) as needed.  The splitting pattern is the comma.  It can be adjusted for more complicated patterns as needed.  For cases where there are fewer than 16 values, the excess will be system missing.  spssinc trans is an extension command that is normally installed with Statistics.

Once you have these values, you can use Analyze > Tables > Multiple Response Sets to define the set and Custom Tables for analysis, assuming that you have the Custom Tables option.

On Mon, Nov 21, 2016 at 8:12 PM, Nancy <[hidden email]> wrote:

Hello,

I have inherited a data set that with a column called "Class_Type" with that contains values split by a semicolon.  I'd like to put each item into its own column.  Here is a sample of 3 rows of data:

row 1:  agriculture; farming; finance; art
row 2:  education
row 3:  art; education; farming; finance; math; science

Some rows have one value; others have more.  There are 16 possible values that can be entered into each field.  Therefore, I'm interested in creating 16 separate columns.  We can call them "Class 1" through "Class 16."  With the data above, row 1 would have "agriculture" in the "Class 1" column; "farming" in the "Class 2" column and so forth.  The fields "Class 5" through "Class 16" would be blank for that row.

Basically, I think I need some syntax that looks for the ";" to act as a stop but I'm not familiar with how to do that.

Again, I inherited this.  No need to berate me for sloppy set up.  Thanks for the help, all.  Really appreciate it.

Nancy

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

--

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Jon Peck]

Correction: the split values are strings in this case, so the first line should read

spssinc trans result=z1 to z16 type=number

where number is the maximum width of a value.

On Mon, Nov 21, 2016 at 8:12 PM, Nancy <[hidden email]> wrote:

Hello,

I have inherited a data set that with a column called "Class_Type" with that contains values split by a semicolon.  I'd like to put each item into its own column.  Here is a sample of 3 rows of data:

row 1:  agriculture; farming; finance; art
row 2:  education
row 3:  art; education; farming; finance; math; science

Some rows have one value; others have more.  There are 16 possible values that can be entered into each field.  Therefore, I'm interested in creating 16 separate columns.  We can call them "Class 1" through "Class 16."  With the data above, row 1 would have "agriculture" in the "Class 1" column; "farming" in the "Class 2" column and so forth.  The fields "Class 5" through "Class 16" would be blank for that row.

Basically, I think I need some syntax that looks for the ";" to act as a stop but I'm not familiar with how to do that.

Again, I inherited this.  No need to berate me for sloppy set up.  Thanks for the help, all.  Really appreciate it.

Nancy

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

--

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Timothy Hennigar]

and ya I know - this is SPSS .. but some of these things can also be done REAL simply in excel and you can do anything from export and merge, cut and paste .. etc kinda depends on how much replication or proper analytic procedure you want etc .. back and forth into SPSS ..  just saying .. for some of those who dont really know SPSS maybe you're better off using something else

---

From: SPSSX(r) Discussion <[hidden email]> on behalf of Nancy <[hidden email]>
Sent: November 21, 2016 10:12 PM
To: [hidden email]
Subject: splitting up a column with ";" delimiter

 

Hello,

I have inherited a data set that with a column called "Class_Type" with that contains values split by a semicolon.  I'd like to put each item into its own column.  Here is a sample of 3 rows of data:

row 1:  agriculture; farming; finance; art
row 2:  education
row 3:  art; education; farming; finance; math; science

Some rows have one value; others have more.  There are 16 possible values that can be entered into each field.  Therefore, I'm interested in creating 16 separate columns.  We can call them "Class 1" through "Class 16."  With the data above, row 1 would have "agriculture" in the "Class 1" column; "farming" in the "Class 2" column and so forth.  The fields "Class 5" through "Class 16" would be blank for that row. 

Basically, I think I need some syntax that looks for the ";" to act as a stop but I'm not familiar with how to do that.

Again, I inherited this.  No need to berate me for sloppy set up.  Thanks for the help, all.  Really appreciate it.

Nancy

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[PRogman]

Assuming your data file (test.dat, below) looks something like this, including a record id:

1;agriculture; farming; finance; art              
2;education                                      
3;art; education; farming; finance; math; science

in SPSS syntax (you have to edit the FILE statement to match your file path and data file name):

*read id and 16 strings of maximum 20 characters*.
DATA LIST
  FILE='%userprofile%\desktop\test.dat'
  LIST (';')
  /id v1 to V16 (F8.0 16A20).
EXECUTE.

It will generate an error message stating 'insufficient data to fulfill the variable list...' for each case with less than 16 variables, and fill the remaining variables with $sysmis or blanks according to type .
HTH, PRogman