parsing and extracting values from a string variable
Locked
parsing and extracting values from a string variable
 
|
Hello everyone, I have the following string variable: Living Room: 4 Bedroom 1: 4 Bedroom 2: 4 Bedroom 3: N/A I would like to parse this variable into one with the text preceding the colon and one with the data AFTER THE COLON into a separate string and eventually numerical variable. The colon and one space always precede the data of interest.
Would someone be willing to help? Thanks so much! Bozena |
Re: parsing and extracting values from a string variable
Administrator
|
Here is a general approach you could use.
1. Use the STRING command to create two new string variables (e.g., Part1 and Part2). 2. Use the CHAR.INDEX function to find the location of the colon in the original string variable. 3. Use the CHAR.SUBSTR function (and the location of the colon) to assign part of the original string variable left of the colon to Part1. 4. Use CHAR.SUBSTR to assign the numeric (or N/A) portion of the original string to Part2. 5. Use RECODE to change 'N/A' to '8' or '88' (or some other numeric code for N/A) in Part2. 6. Use ALTER TYPE to change Part2 from string to numeric. 7. Assign a value label of 'N/A' to the 8 or 88 (or whatever your N/A numeric code is) for Part2. I was going to post code, but you'll gain more from looking up examples and working through it yourself than you would from receiving a fully cooked fish. ;-)
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: parsing and extracting values from a string variable
|
|
Thank you, Bruce. It was fun. This is what I came up with to parse the variable "kitchen4" into "location" (text before the colon) and "code" (text after the colon). Worked fine but please let me know if there was a simpler way.
Thanks again. Bozena STRING code (A6). COMPUTE #colon= CHAR.INDEX(kitchen4, ':'). COMPUTE code = CHAR.SUBSTR (kitchen4, #colon+1). EXECUTE. STRING location (A16). COMPUTE #colon= CHAR.INDEX(kitchen4, ':'). COMPUTE location = CHAR.SUBSTR (kitchen4, 1, #colon-1). EXECUTE. -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bruce Weaver Sent: August 18, 2015 1:12 PM To: [hidden email] Subject: Re: parsing and extracting values from a string variable Here is a general approach you could use. 1. Use the STRING command to create two new string variables (e.g., Part1 and Part2). 2. Use the CHAR.INDEX function to find the location of the colon in the original string variable. 3. Use the CHAR.SUBSTR function (and the location of the colon) to assign part of the original string variable left of the colon to Part1. 4. Use CHAR.SUBSTR to assign the numeric (or N/A) portion of the original string to Part2. 5. Use RECODE to change 'N/A' to '8' or '88' (or some other numeric code for N/A) in Part2. 6. Use ALTER TYPE to change Part2 from string to numeric. 7. Assign a value label of 'N/A' to the 8 or 88 (or whatever your N/A numeric code is) for Part2. I was going to post code, but you'll gain more from looking up examples and working through it yourself than you would from receiving a fully cooked fish. ;-) Zdaniuk, Bozena-3 wrote > Hello everyone, I have the following string variable: > Living Room: 4 > Bedroom 1: 4 > Bedroom 2: 4 > Bedroom 3: N/A > I would like to parse this variable into one with the text preceding > the colon and one with the data AFTER THE COLON into a separate string > and eventually numerical variable. The colon and one space always > precede the data of interest. Would someone be willing to help? Thanks so much! > Bozena > > > ===================== > 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/parsing-and-extracting-values-from-a-string-variable-tp5730472p5730476.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 ===================== 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 |
Re: parsing and extracting values from a string variable
|
Administrator
|
That's more or less what I had in mind--although you have an unnecessary EXECUTE. If you remove it, you won't have to compute #colon twice.
Also, I thought you wanted your variable code to be converted to a numeric variable ultimately. That's why I suggested RECODE and ALTER TYPE. Something like this (untested, as I have no SPSS on this machine): STRING location (A16) / code (A6). COMPUTE #colon= CHAR.INDEX(kitchen4, ':'). COMPUTE location = CHAR.SUBSTR (kitchen4, 1, #colon-1). COMPUTE code = CHAR.SUBSTR (kitchen4, #colon+1). RECODE code ('N/A' = '88'). /* Assuming 88 is not a valid value. ALTER TYPE code (F6.0). VALUE LABELS code 88 'N/A' . FREQUENCIES code. HTH.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: parsing and extracting values from a string variable
Administrator
|
Good to consider the situation where there is a data mishap?
STRING location (A16) / code (A6). NUMERIC Data_Error (F1). RECODE Data_Error (ELSE=0). COMPUTE #colon= CHAR.INDEX(kitchen4, ':'). DO IF #colon GT 1. + COMPUTE location = CHAR.SUBSTR (kitchen4, 1, #colon-1). + COMPUTE code = CHAR.SUBSTR (kitchen4, #colon+1). + RECODE code ('N/A' = '88'). /* Assuming 88 is not a valid value. ELSE. + COMPUTE Data_ERROR=1. END IF. ALTER TYPE code (F6.0). VALUE LABELS code 88 'N/A' . FREQUENCIES code Data_Error.
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: parsing and extracting values from a string variable
|
Administrator
|
Yep...good idea!
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: parsing and extracting values from a string variable
|
|
In reply to this post by David Marso
Thanks so much to Bruce and David!
Bozena -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso Sent: August 19, 2015 11:55 AM To: [hidden email] Subject: Re: parsing and extracting values from a string variable Good to consider the situation where there is a data mishap? STRING location (A16) / code (A6). NUMERIC Data_Error (F1). RECODE Data_Error (ELSE=0). COMPUTE #colon= CHAR.INDEX(kitchen4, ':'). DO IF #colon GT 1. + COMPUTE location = CHAR.SUBSTR (kitchen4, 1, #colon-1). + COMPUTE code = CHAR.SUBSTR (kitchen4, #colon+1). + RECODE code ('N/A' = '88'). /* Assuming 88 is not a valid value. ELSE. + COMPUTE Data_ERROR=1. END IF. ALTER TYPE code (F6.0). VALUE LABELS code 88 'N/A' . FREQUENCIES code Data_Error. Bruce Weaver wrote > That's more or less what I had in mind--although you have an > unnecessary EXECUTE. If you remove it, you won't have to compute #colon twice. > > Also, I thought you wanted your variable code to be converted to a > numeric variable ultimately. That's why I suggested RECODE and ALTER TYPE. > Something like this (untested, as I have no SPSS on this machine): > > STRING location (A16) / code (A6). > COMPUTE #colon= CHAR.INDEX(kitchen4, ':'). > COMPUTE location = CHAR.SUBSTR (kitchen4, 1, #colon-1). > COMPUTE code = CHAR.SUBSTR (kitchen4, #colon+1). > RECODE code ('N/A' = '88'). /* Assuming 88 is not a valid value. > ALTER TYPE code (F6.0). > VALUE LABELS code > 88 'N/A' > . > FREQUENCIES code. > > HTH. > > Zdaniuk, Bozena-3 wrote >> Thank you, Bruce. It was fun. This is what I came up with to parse >> the variable "kitchen4" into "location" (text before the colon) and "code" >> (text after the colon). Worked fine but please let me know if there >> was a simpler way. >> Thanks again. >> Bozena >> >> STRING code (A6). >> COMPUTE #colon= CHAR.INDEX(kitchen4, ':'). >> COMPUTE code = CHAR.SUBSTR (kitchen4, #colon+1). >> EXECUTE. >> >> STRING location (A16). >> COMPUTE #colon= CHAR.INDEX(kitchen4, ':'). >> COMPUTE location = CHAR.SUBSTR (kitchen4, 1, #colon-1). >> EXECUTE. >> >> >> -----Original Message----- >> From: SPSSX(r) Discussion [mailto: >> SPSSX-L@.UGA >> ] On Behalf Of Bruce Weaver >> Sent: August 18, 2015 1:12 PM >> To: >> SPSSX-L@.UGA >> Subject: Re: parsing and extracting values from a string variable >> >> Here is a general approach you could use. >> >> 1. Use the STRING command to create two new string variables (e.g., >> Part1 and Part2). >> 2. Use the CHAR.INDEX function to find the location of the colon in >> the original string variable. >> 3. Use the CHAR.SUBSTR function (and the location of the colon) to >> assign part of the original string variable left of the colon to Part1. >> 4. Use CHAR.SUBSTR to assign the numeric (or N/A) portion of the >> original string to Part2. >> 5. Use RECODE to change 'N/A' to '8' or '88' (or some other numeric >> code for >> N/A) in Part2. >> 6. Use ALTER TYPE to change Part2 from string to numeric. >> 7. Assign a value label of 'N/A' to the 8 or 88 (or whatever your N/A >> numeric code is) for Part2. >> >> I was going to post code, but you'll gain more from looking up >> examples and working through it yourself than you would from >> receiving a fully cooked fish. ;-) >> >> >> >> Zdaniuk, Bozena-3 wrote >>> Hello everyone, I have the following string variable: >>> Living Room: 4 >>> Bedroom 1: 4 >>> Bedroom 2: 4 >>> Bedroom 3: N/A >>> I would like to parse this variable into one with the text preceding >>> the colon and one with the data AFTER THE COLON into a separate >>> string and eventually numerical variable. The colon and one space >>> always precede the data of interest. Would someone be willing to >>> help? Thanks so much! >>> Bozena >>> >>> >>> ===================== >>> 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 >> bweaver@ >> 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/parsing-and-extracting- >> values-from-a-string-variable-tp5730472p5730476.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 >> >> ===================== >> 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 ----- Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/parsing-and-extracting-values-from-a-string-variable-tp5730472p5730480.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 ===================== 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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |