Extracting a dollar value from a string
Locked
Extracting a dollar value from a string
 
|
(The question I'm responding to here was posted as a continuation to
an earlier thread. I'm following good practice, in changing the subject line when a new topic is raised.) At 11:20 AM 12/12/2013, Dammann, Ross wrote (under subject line "RE: How to calculate time difference (minutes) in syntax?"): >I'm trying to select a portion of a string variable (see below). The >length I need returned from the variable varies from 5 to 11 >characters. Essentially, I want to be able to grab the dollar value >from the variable below, including 2 decimal places. > >$4,295.0012/1/1305/31/2014REGNEU726188534A >$22.768/27/1310/12/2013REGMSCZ00060100A > >I've created a column that indicates the length of the variable I >want, and want to be able to reference that in a SUBSTR script. For >example, I have a length column that indicates 9 for the 1st record >and 6 for the 2nd record above. It's very direct. If your new variable is called, say, 'DollarLen', your original string variable is 'InputString', and you want the dollar value as a *string* variable, then (all code is tested) STRING DollarStr (A9). COMPUTE DollarStr =SUBSTR(InputString,1,Dollarlen). If you want it as a *numeric* variable, then NUMERIC DollarNum (DOLLAR9.2). COMPUTE DollarNum =NUMBER(SUBSTR(InputString,1,Dollarlen),DOLLAR9.2). Finally, I think it's more readable, and saves having the length variable saved permanently in the file, if you use scratch variables. I freely admit that this uses 12 lines where 2 could be made to do. *WARNING*: You *must not* have an EXECUTE between the point where you define a scratch variable and the one where you use it. NUMERIC Dollar2 (DOLLAR9.2). NUMERIC #DLen (F3). STRING #DString (A12). *. Compute the length of the 'dollar' value: two places past the first . *. decimal point. ** THIS MAY NOT BE TRUE IN EVERY CASE IN THE DATA ** . COMPUTE #Dlen = INDEX(InputString,'.') + 2. COMPUTE #DString = SUBSTR(InputString,1,Dollarlen). COMPUTE Dollar2 = NUMBER(#DString,DOLLAR12.2). ================================== APPENDIX I: Listing ================================== * The dollar value, as a string variable . STRING DollarStr (A9). COMPUTE DollarStr =SUBSTR(InputString,1,Dollarlen). * The dollar value, as a numeric variable . NUMERIC DollarNum (DOLLAR9.2). COMPUTE DollarNum =NUMBER(SUBSTR(InputString,1,Dollarlen),DOLLAR9.2). * The dollar value as a numeric variable, using scratch variables . * for the computation. . NUMERIC Dollar2 (DOLLAR9.2). NUMERIC #DLen (F3). STRING #DString (A12). *. Compute the length of the 'dollar' value: two places past the first . *. decimal point. ** THIS MAY NOT BE TRUE IN EVERY CASE IN THE DATA ** . COMPUTE #Dlen = INDEX(InputString,'.') + 2. COMPUTE #DString = SUBSTR(InputString,1,Dollarlen). COMPUTE Dollar2 = NUMBER(#DString,DOLLAR12.2). LIST. List |-----------------------------|---------------------------| |Output Created |12-DEC-2013 13:00:28 | |-----------------------------|---------------------------| Dol lar InputString Len DollarStr DollarNum Dollar2 $4,295.0012/1/1305/31/2014REGNEU726188534A 9 $4,295.00 $4,295.00 $4,295.00 $22.768/27/1310/12/2013REGMSCZ00060100A 6 $22.76 $22.76 $22.76 Number of cases read: 2 Number of cases listed: 2 ================================== APPENDIX II: Test data, and code ================================== DATA LIST FIXED /InputString (A45). BEGIN DATA $4,295.0012/1/1305/31/2014REGNEU726188534A $22.768/27/1310/12/2013REGMSCZ00060100A END DATA. * Compute the length of the 'dollar' value: two places past the first . * decimal point. ** THIS MAY NOT BE TRUE IN EVERY CASE IN THE DATA ** . NUMERIC DollarLen (F3). COMPUTE DollarLen = INDEX(InputString,'.') + 2. * The dollar value, as a string variable . STRING DollarStr (A9). COMPUTE DollarStr =SUBSTR(InputString,1,Dollarlen). * The dollar value, as a numeric variable . NUMERIC DollarNum (DOLLAR9.2). COMPUTE DollarNum =NUMBER(SUBSTR(InputString,1,Dollarlen),DOLLAR9.2). * The dollar value as a numeric variable, using scratch variables . * for the computation. . NUMERIC Dollar2 (DOLLAR9.2). NUMERIC #DLen (F3). STRING #DString (A12). *. Compute the length of the 'dollar' value: two places past the first . *. decimal point. ** THIS MAY NOT BE TRUE IN EVERY CASE IN THE DATA ** . COMPUTE #Dlen = INDEX(InputString,'.') + 2. COMPUTE #DString = SUBSTR(InputString,1,Dollarlen). COMPUTE Dollar2 = NUMBER(#DString,DOLLAR12.2). LIST. ===================== 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 |
|
Thanks Richard. You're right the EXECUTE statements are not needed, thanks for the tip. And I appreciate the help. While we are communicating, I'm trying to select a portion of a string variable (see below). The length I need returned from the variable varies from 5 to 11 characters. Essentially, I want to be able to grab the dollar value from the
variable below, including 2 decimal places. $4,295.0012/1/1305/31/2014REGNEU726188534A $22.768/27/1310/12/2013REGMSCZ00060100A I've created a column that indicates the length of the variable I want, and want to be able to reference that in a SUBSTR script. For example, I have a length column that indicates 9 for the 1st record and 6 for the 2nd record above. How can I do that? Thanks Ross -----Original Message----- At 11:34 AM 12/9/2013, rtdammann wrote: >The data comes as dd/mm/yyyy hh:mm in one column. >Opened_date = 7/2/2013 11:37 >Closed_date = 7/8/2013 14:32 First of all, get rid of all those Execute statements! They do nothing for you, except to require the data to be re-read for each one. 'Execute' is *not* necessary for a transformation command to take effect. Now, I'm following David Marso's thinking: you want the total elapsed time, counting 9 hours for each elapsed day (so, 540 minutes -- why do you have >Compute daily_minutes = days*160. (because its a 9 hour day) in your syntax?) Assuming that the dates and times are in separate SPSS variables, I think this will work: COMPUTE Total_Minutes = 540 * CTIME.DAYS (CloseDate - OpenDate) + CTIME.MINUTES(CloseTime - OpenTime). LIST. List |-----------------------------|---------------------------| |Output Created |10-DEC-2013 18:26:10 | |-----------------------------|---------------------------| OpenDate OpenTime CloseDate CloseTime Total_Minutes 07/02/2013 11:37 07/08/2013 14:32 3415.00 Number of cases read: 1 Number of cases listed: 1 ============================= APPENDIX: Test data, and code ============================= DATA LIST LIST /OpenDate OpenTime CloseDate CloseTime (ADATE10, TIME5, ADATE10, TIME5). BEGIN DATA 7/2/2013 11:37 7/8/2013 14:32 END DATA. COMPUTE Total_Minutes = 540 * CTIME.DAYS (CloseDate - OpenDate) + CTIME.MINUTES(CloseTime - OpenTime). LIST. |
Re: How to calculate time difference (minutes) in syntax?
Administrator
|
See CHAR.INDEX and CHAR.SUBSTR functions in the FM . BTW, there is no need to create any length field. On Thu, Dec 12, 2013 at 2:27 PM, rtdammann [via SPSSX Discussion] <[hidden email]> wrote:
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: How to calculate time difference (minutes) in syntax?
|
|
In reply to this post by rtdammann
At 11:20 AM 12/12/2013, Dammann, Ross wrote:
>I'm trying to select a portion of a string variable ... Substantive reply posted under subject heading "Extracting a dollar value from a string", to encourage the practice of using a new subject line when a new topic is raised. ===================== 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 |
| Free forum by Nabble | Edit this page |