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.
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;
Remove the $ with compress statement and then convert to a numeric variable with an input function.
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?
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: