topic Convert Amount field from character to numeric in SAS Programming

Convert Amount field from character to numeric

Leonoids — Tue, 26 May 2020 04:59:57 GMT

Hello,

I am trying to convert an amount field from character to numeric using input function but unable to do.

Getting error Invalid argument to input function.

I have tried commaw.d , bestw.d informats as well but its not working.

amount=input(amount_old,20.2);

example: data in amount_old field

23.45

12,300.56

123,123.23

Thanks in advance.

Re: Convert Amount field from character to numeric

s_lassen — Tue, 26 May 2020 06:14:41 GMT

Not sure what you have been doing, but this seems to work fine:

data have;
  length amount_old $20;
  input amount_old;
  amount=input(amount_old,comma20.2);
cards;
23.45
12,300.56
123,123.23
;run;

Re: Convert Amount field from character to numeric

ChrisNZ — Tue, 26 May 2020 07:44:25 GMT

The dollar. informat is the most flexible one. It handles commas, dollar signs and plain numbers.

Re: Convert Amount field from character to numeric

Leonoids — Wed, 27 May 2020 11:51:20 GMT

Thank you.

I can able to convert from char to numeric while creating dataset using datalines.

I have pipe delimited file in which trying to process the data but its not happening.

data one;

infile "filename" delimiter='|' missover dsd lrecl=2000;

informat amount comma26.2;

length amount 8;

input amount;

run;

error: Invalid data for amount

Re: Convert Amount field from character to numeric

Leonoids — Wed, 27 May 2020 11:55:45 GMT

for the above, if I am keeping amount column as character I can able to create the dataset without any errors. once the dataset creates, trying to convert amount field from char to numeric, getting error invalid argument to input function.

Re: Convert Amount field from character to numeric

Tom — Wed, 27 May 2020 14:16:24 GMT

@ChrisNZ wrote:

The dollar. informat is the most flexible one. It handles commas, dollar signs and plain numbers.

The DOLLAR informat is just an alias for the COMMA informat.

Re: Convert Amount field from character to numeric

Tom — Wed, 27 May 2020 14:31:43 GMT

Did you really intend to divide the integer values by 100? If not then do NOT include a decimal part in the INFORMAT. The purpose of the decimal part of an informat is to tell SAS that the decimal point is explicitly NOT present in the data to save one character in representing the number as text. The maximum width that the COMMA informat supports is 32 and the INPUT() function does not mind if the width you use on the informat is larger than the length of the string you are reading.

amount=input(amount_old,comma32.);

Re: Convert Amount field from character to numeric

ChrisNZ — Wed, 27 May 2020 23:05:26 GMT

So it is! Thank you!