Variable with values separated by commas

[Sarah T]

Hello everyone!

I have a variable in my data file that contains six individual fields, each separated by commas. So, one value may look something like 1,13,5,6,99,8. What I'm looking to do is separate this out into 6 separate variables, the first would have a value of 1, the second 13, the third 5, etc.

I can't figure out the most efficient way to do this. At first I was thinking substring, but each field is not an equal length. Ideally, the command would some equivalent of Excel's 'text to columns', delimited with the commas.

Any thoughts?

Thank you!

[David Marso]

See INDEX function in conjunction with SUBSTR
eg: http://spssx-discussion.1045642.n5.nabble.com/Change-string-to-date-variable-td1075756i20.html#a2834460

[Jon K Peck]

If you have installed the Python Essentials and the SPSSINC TRANS extension command, this will do it more easily than using index searching.

data list free /x(a13).
begin data.
1,13,5,6,99,8
end data.
dataset name data.

begin program.
def split(v):
  return v.split(',')
end program.

spssinc trans result = v1 to v6
/formula "split(x)".

Regards,

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
new phone: [phone]




From:        Sarah T <[hidden email]>
To:        [hidden email]
Date:        07/20/2012 12:55 PM
Subject:        [SPSSX-L] Variable with values separated by commas
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hello everyone!

I have a variable in my data file that contains six individual fields, each
separated by commas. So, one value may look something like 1,13,5,6,99,8.
What I'm looking to do is separate this out into 6 separate variables, the
first would have a value of 1, the second 13, the third 5, etc.

I can't figure out the most efficient way to do this. At first I was
thinking substring, but each field is not an equal length. Ideally, the
command would some equivalent of Excel's 'text to columns', delimited with
the commas.

Any thoughts?

Thank you!



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Variable-with-values-separated-by-commas-tp5714349.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
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

[MaxJasper]

Open a new SPSS file and then read into it your data as text…in the dialogue boxes that appear next specify comma as separator.

 

Max.

 

From: Sarah T [via SPSSX Discussion] [mailto:[hidden email]]
Sent: 2012-Jul-20 Friday 12:45
To: MaxJasper
Subject: Variable with values separated by commas

 

Hello everyone!

I have a variable in my data file that contains six individual fields, each separated by commas. So, one value may look something like 1,13,5,6,99,8. What I'm looking to do is separate this out into 6 separate variables, the first would have a value of 1, the second 13, the third 5, etc.

I can't figure out the most efficient way to do this. At first I was thinking substring, but each field is not an equal length. Ideally, the command would some equivalent of Excel's 'text to columns', delimited with the commas.

Any thoughts?

Thank you!

---

If you reply to this email, your message will be added to the discussion below:

http://spssx-discussion.1045642.n5.nabble.com/Variable-with-values-separated-by-commas-tp5714349.html

To start a new topic under SPSSX Discussion, email [hidden email]
To unsubscribe from SPSSX Discussion, click here.
NAML

[Rick Oliver-3]

Or if you haven't installed Python, the old-fashioned way:

****sample data***.
begin data
1,23,456,7,89,10
end data.
***code starts here***.
string #temp (a20).
compute #temp=var1.
vector newvar(6, f4).
loop #i=1 to 6.
do if index(#temp, ",")>0.
compute #index=char.index(#temp, ",").
compute newvar(#i)=number(substr(#temp, 1, #index-1), f4).
compute #temp=substr(#temp, #index+1).
else.
compute newvar(#i)=number(#temp, f4).
end if.
end loop.
list.


Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]




From:        Jon K Peck/Chicago/IBM@IBMUS
To:        [hidden email],
Date:        07/20/2012 03:14 PM
Subject:        Re: Variable with values separated by commas
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

If you have installed the Python Essentials and the SPSSINC TRANS extension command, this will do it more easily than using index searching.

data list free /x(a13).
begin data.
1,13,5,6,99,8
end data.
dataset name data.

begin program.
def split(v):
 return v.split(',')
end program.

spssinc trans result = v1 to v6
/formula "split(x)".

Regards,

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
new phone: [phone]




From:        Sarah T <[hidden email]>
To:        [hidden email]
Date:        07/20/2012 12:55 PM
Subject:        [SPSSX-L] Variable with values separated by commas
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hello everyone!

I have a variable in my data file that contains six individual fields, each
separated by commas. So, one value may look something like 1,13,5,6,99,8.
What I'm looking to do is separate this out into 6 separate variables, the
first would have a value of 1, the second 13, the third 5, etc.

I can't figure out the most efficient way to do this. At first I was
thinking substring, but each field is not an equal length. Ideally, the
command would some equivalent of Excel's 'text to columns', delimited with
the commas.

Any thoughts?

Thank you!



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Variable-with-values-separated-by-commas-tp5714349.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
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

[Bruce Weaver]

Oops...you left off the DATA LIST line for generating the sample data.  ;-)

****sample data***.
DATA LIST / var1(a20).
begin data
1,23,456,7,89,10
end data.
*** etc. *** .

OUTPUT:

var1                 newvar1 newvar2 newvar3 newvar4 newvar5 newvar6
 
1,23,456,7,89,10          1      23     456       7      89      10
 
Number of cases read:  1    Number of cases listed:  1

.oO(Now why anyone would want to use a straight-forward method like that when they could be fiddling around installing Python -- making sure they have the correct version, etc -- is beyond me!)  

Oops...was that out loud?  Sorry Jon...couldn't resist a little poke late on a Friday afternoon.  ;-)

Rick Oliver wrote

Or if you haven't installed Python, the old-fashioned way:

****sample data***.
begin data
1,23,456,7,89,10
end data.
***code starts here***.
string #temp (a20).
compute #temp=var1.
vector newvar(6, f4).
loop #i=1 to 6.
do if index(#temp, ",")>0.
compute #index=char.index(#temp, ",").
compute newvar(#i)=number(substr(#temp, 1, #index-1), f4).
compute #temp=substr(#temp, #index+1).
else.
compute newvar(#i)=number(#temp, f4).
end if.
end loop.
list.


Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]




From:   Jon K Peck/Chicago/IBM@IBMUS
To:     [hidden email],
Date:   07/20/2012 03:14 PM
Subject:        Re: Variable with values separated by commas
Sent by:        "SPSSX(r) Discussion" <[hidden email]>



If you have installed the Python Essentials and the SPSSINC TRANS
extension command, this will do it more easily than using index searching.


data list free /x(a13).
begin data.
1,13,5,6,99,8
end data.
dataset name data.

begin program.
def split(v):
  return v.split(',')
end program.

spssinc trans result = v1 to v6
/formula "split(x)".

Regards,

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
new phone: [phone]




From:        Sarah T <[hidden email]>
To:        [hidden email]
Date:        07/20/2012 12:55 PM
Subject:        [SPSSX-L] Variable with values separated by commas
Sent by:        "SPSSX(r) Discussion" <[hidden email]>



Hello everyone!

I have a variable in my data file that contains six individual fields,
each
separated by commas. So, one value may look something like 1,13,5,6,99,8.
What I'm looking to do is separate this out into 6 separate variables, the
first would have a value of 1, the second 13, the third 5, etc.

I can't figure out the most efficient way to do this. At first I was
thinking substring, but each field is not an equal length. Ideally, the
command would some equivalent of Excel's 'text to columns', delimited with
the commas.

Any thoughts?

Thank you!



--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/Variable-with-values-separated-by-commas-tp5714349.html

Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
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

[Rick Oliver-3]

Oops. Thanks, Bruce.


Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]
Phone: [phone] | T/L: 206-4922



From:        Bruce Weaver <[hidden email]>
To:        [hidden email],
Date:        07/20/2012 04:51 PM
Subject:        Re: Variable with values separated by commas
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Oops...you left off the DATA LIST line for generating the sample data.  ;-)

****sample data***.
DATA LIST / var1(a20).
begin data
1,23,456,7,89,10
end data.
*** etc. *** .

OUTPUT:

var1                 newvar1 newvar2 newvar3 newvar4 newvar5 newvar6

1,23,456,7,89,10          1      23     456       7      89      10

Number of cases read:  1    Number of cases listed:  1

.oO(Now why anyone would want to use a straight-forward method like that
when they could be fiddling around installing Python -- making sure they
have the correct version, etc -- is beyond me!)

Oops...was that out loud?  Sorry Jon...couldn't resist a little poke late on
a Friday afternoon.  ;-)



Rick Oliver wrote
>
> Or if you haven't installed Python, the old-fashioned way:
>
> ****sample data***.
> begin data
> 1,23,456,7,89,10
> end data.
> ***code starts here***.
> string #temp (a20).
> compute #temp=var1.
> vector newvar(6, f4).
> loop #i=1 to 6.
> do if index(#temp, ",")>0.
> compute #index=char.index(#temp, ",").
> compute newvar(#i)=number(substr(#temp, 1, #index-1), f4).
> compute #temp=substr(#temp, #index+1).
> else.
> compute newvar(#i)=number(#temp, f4).
> end if.
> end loop.
> list.
>
>
> Rick Oliver
> Senior Information Developer
> IBM Business Analytics (SPSS)
> E-mail: oliverr@.ibm
>
>
>
>
> From:   Jon K Peck/Chicago/IBM@IBMUS
> To:     SPSSX-L@.uga,
> Date:   07/20/2012 03:14 PM
> Subject:        Re: Variable with values separated by commas
> Sent by:        "SPSSX(r) Discussion" &lt;SPSSX-L@.uga&gt;
>
>
>
> If you have installed the Python Essentials and the SPSSINC TRANS
> extension command, this will do it more easily than using index searching.
>
>
> data list free /x(a13).
> begin data.
> 1,13,5,6,99,8
> end data.
> dataset name data.
>
> begin program.
> def split(v):
>   return v.split(',')
> end program.
>
> spssinc trans result = v1 to v6
> /formula "split(x)".
>
> Regards,
>
> Jon Peck (no "h") aka Kim
> Senior Software Engineer, IBM
> peck@.ibm
> new phone: [phone]
>
>
>
>
> From:        Sarah T &lt;starraf@&gt;
> To:        SPSSX-L@.uga
> Date:        07/20/2012 12:55 PM
> Subject:        [SPSSX-L] Variable with values separated by commas
> Sent by:        "SPSSX(r) Discussion" &lt;SPSSX-L@.uga&gt;
>
>
>
> Hello everyone!
>
> I have a variable in my data file that contains six individual fields,
> each
> separated by commas. So, one value may look something like 1,13,5,6,99,8.
> What I'm looking to do is separate this out into 6 separate variables, the
> first would have a value of 1, the second 13, the third 5, etc.
>
> I can't figure out the most efficient way to do this. At first I was
> thinking substring, but each field is not an equal length. Ideally, the
> command would some equivalent of Excel's 'text to columns', delimited with
> the commas.
>
> Any thoughts?
>
> Thank you!
>
>
>
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/Variable-with-values-separated-by-commas-tp5714349.html
>
> Sent from the SPSSX Discussion mailing list archive at Nabble.com.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> LISTSERV@.UGA (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
>




-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Variable-with-values-separated-by-commas-tp5714349p5714355.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
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