topic Parse dollar amount from text string using Base SAS in SAS Programming

Parse dollar amount from text string using Base SAS

Conundrum — Thu, 23 Jun 2016 22:17:35 GMT

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.

Re: Parse dollar amount from text string using Base SAS

Reeza — Thu, 23 Jun 2016 22:47:16 GMT

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

Re: Parse dollar amount from text string using Base SAS

Conundrum — Thu, 23 Jun 2016 22:59:42 GMT

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?

Re: Parse dollar amount from text string using Base SAS

Reeza — Thu, 23 Jun 2016 23:17:38 GMT

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.

Re: Parse dollar amount from text string using Base SAS

Kurt_Bremser — Fri, 24 Jun 2016 07:49:49 GMT

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;

Re: Parse dollar amount from text string using Base SAS

Conundrum — Fri, 24 Jun 2016 14:37:53 GMT

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.