DATA Step, Macro, Functions and more

Parse dollar amount from text string using Base SAS

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Parse dollar amount from text string using Base SAS

Hello,

 

Can anyone help me with how to parse dollar amounts from a Text variable in Base SAS? The challenge is the textg string doesn't have uniform formats, like $100.50 could be entered as 100.50 or even $ 100.50. The only constant would be '.XX' with varying number of characters to the left of the decimal point. Any help is appreciated.


Accepted Solutions
Solution
‎06-24-2016 10:34 AM
Super User
Posts: 6,936

Re: Parse dollar amount from text string using Base SAS

This should be able to deal with all kinds of circumstances:

data want (drop=i i1 field);
set have;
length field $20;
i = findc(instring,'.',1);
do while (i ne 0);
  if notdigit(substr(instring,i+1,2)) = 0
  then do;
    i1 = i - 1;
    do while (notdigit(substr(instring,i1,1)) = 0);
      i1 + (-1);
    end;
    field = substr(instring,i1+1,i+2-i1);
    amount = input(field,20.2);
    i = 0;
  end;
  else i = findc(instring,'.',i+1);
end;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 17,819

Re: Parse dollar amount from text string using Base SAS

Remove the $ with compress statement and then convert to a numeric variable with an input function. 

New Contributor
Posts: 3

Re: Parse dollar amount from text string using Base SAS

I forgot to add this is a comment field where there is more than just the dollar amount in the text field, it will have for instance, 'amount of $100.50 entered on MM/DD/YYYY due to xyz'. I want to create a new variable that just has the amount of 100.50 for this example. Will this solution work for this scenario?

Super User
Posts: 17,819

Re: Parse dollar amount from text string using Base SAS

No, that's a big difference.

 

Post several of the variations that you have, preferably one of each. The best solution will be a PRX but I'm not good at that.

Solution
‎06-24-2016 10:34 AM
Super User
Posts: 6,936

Re: Parse dollar amount from text string using Base SAS

This should be able to deal with all kinds of circumstances:

data want (drop=i i1 field);
set have;
length field $20;
i = findc(instring,'.',1);
do while (i ne 0);
  if notdigit(substr(instring,i+1,2)) = 0
  then do;
    i1 = i - 1;
    do while (notdigit(substr(instring,i1,1)) = 0);
      i1 + (-1);
    end;
    field = substr(instring,i1+1,i+2-i1);
    amount = input(field,20.2);
    i = 0;
  end;
  else i = findc(instring,'.',i+1);
end;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 3

Re: Parse dollar amount from text string using Base SAS

This worked great and exactly what I needed. I will tweak this code for the instances where there was no decimal point and only had like '$20' for the amount.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 258 views
  • 1 like
  • 3 in conversation