topic convert character price to numeric in SAS Studio

convert character price to numeric

RebeccaJW — Mon, 29 Apr 2019 16:25:39 GMT

Hello, I am struggling with conversion of a character price to numeric. The data looks like this:

As there is dollar sign there, I did the following. But there is error, and all prices turn out to be 0. Could anyone help me?

	if Price ~=0 then Price_num = input(Price, dollar10.3);
	else Price_num = 0;

Re: convert character price to numeric

novinosrin — Mon, 29 Apr 2019 16:29:39 GMT


data have;
input price $;
cards;
0
$9.99
;

data want;
set have;
price_num=ifn(price ne '0',input(price,comma10.2),0);
format price_num dollar10.2;
run;

Re: convert character price to numeric

Tom — Mon, 29 Apr 2019 16:37:36 GMT

Don't compare character strings to numbers. SAS will have to convert one or the other to make the comparison.

Don't add a decimal place value to an INFORMAT. SAS will add an implied decimal point to integer values. So if you read $123,456 using DOLLAR10.3 informat the value will be 123.456 instead of 123456.

Make sure the width of the informat matches the width of the character variable.

Note that there is no difference between the DOLLAR informat and the COMMA informat. Both will ignore dollar signs and commas in the string.

Price_num = input(Price,comma32.);

Also you might need to watch out for right aligned values in the character variable. Might not be obvious as in most places SAS will not display the leading spaces. If in doubt print the values using $QUOTE. format and you should be able to see the leading spaces. Ite will be an issue if the width of the informat is too short to reach the end of the actual digits in the character variable.

Re: convert character price to numeric

ballardw — Mon, 29 Apr 2019 16:30:49 GMT

Try something like Comma12.2 instead of Dollar19,3

Do you actually have values with 3 decimals? US currency generally only has 2.

Re: convert character price to numeric

Tom — Mon, 29 Apr 2019 16:49:41 GMT

Remember that decimal places on INFORMATs are inappropriate unless you KNOW that the strings were specifically created to support the implied decimal point that means. It won't matter if all of the values are 0, missing, or contain an actual period character. But if some are just digits without any period then resulting value will be divided by 10 to the power of the number of decimal places specified in the informat.

Re: convert character price to numeric

RebeccaJW — Mon, 29 Apr 2019 16:50:43 GMT

Thank you so much for your help! I appreciate it!

Re: convert character price to numeric

RebeccaJW — Mon, 29 Apr 2019 16:50:54 GMT

Thank you! I solve that!

Re: convert character price to numeric

RebeccaJW — Mon, 29 Apr 2019 16:51:14 GMT

Thank you for pointing out it!

Re: convert character price to numeric

RebeccaJW — Mon, 29 Apr 2019 16:51:25 GMT

Thank you for explanation!