DATA Step, Macro, Functions and more

problem to convert a character into numeric with comma

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

problem to convert a character into numeric with comma

hello,

 

in a dataset , there are 2 records and a column named "amount"  (character), and values are

rec         amount

1           82.04

2          108498.19

 

And I would like to convert these values into numeric.

I try input(amount,5.) that returns 82.04 for rec1 (correct) but 10849 for rec 2(not correct because truncated)

I try input(amount,9.) that returns 108498.19 for rec 2 (correct) but "." for rec 1 (not correct)

 

please, what is the correct syntax and the correct informat ?

thanks in advance


Accepted Solutions
Solution
‎06-07-2017 11:03 AM
Super User
Super User
Posts: 7,039

Re: problem to convert a character into numeric with comma

Posted in reply to Nasser_alfea

Are you reading from a text file? Make sure to use the TRUNCOVER option on the INFILE statement.  Or add the : modifier in front of the format in the INPUT statement.

Are you reading from a character variable using the INPUT() function? Is so then your issue is probably caused by non-printing characters on the end of the shorter string.  Like at TAB ('09'x) or a CR ('0D'x) or perhaps a NUL ('00'x) or what Microsoft likes to call a non-breaking space ('A0'x).  Try printing the value using $HEX format to see what characters are there and then use the COMPRESS() function to remove them.

If you want the value to print with commas as the thousand separators then use the COMMA format. If you want to change decimal point to a comma and use period as thousands separator then use COMMAX or change your locale settings.

 

data want ;
 set have ;
  amount_num = input(amount,32.);
  format amount_num comma14.2;
run;

 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,913

Re: problem to convert a character into numeric with comma

Posted in reply to Nasser_alfea

To convert to numeric

 

amount1 = amount+0;

To include a comma, this is done via assigning the comma format to amount1

 

PROC Star
Posts: 736

Re: problem to convert a character into numeric with comma

Posted in reply to Nasser_alfea
data have;
length amount $10;
input rec amount$;
datalines;
1 82.04
2 108498.19
;

data want;
	set have;
	amount_num = input(amount,comma10.2);
run;
Trusted Advisor
Posts: 1,913

Re: problem to convert a character into numeric with comma


draycut wrote:
data have;
length amount $10;
input rec amount$;
datalines;
1 82.04
2 108498.19
;

data want;
	set have;
	amount_num = input(amount,comma10.2);
run;

When I execute this code and then do a proc print, I don't see the commas in amount_num.

PROC Star
Posts: 736

Re: problem to convert a character into numeric with comma

Posted in reply to PaigeMiller

Strange, I do? Smiley Happy

 

Udklip.PNG

Trusted Advisor
Posts: 1,913

Re: problem to convert a character into numeric with comma


draycut wrote:

Strange, I do? Smiley Happy

 

Udklip.PNG


Maybe there's something unusual about my computer and commas are written in invisible ink, but I do not see commas, not in my web browser looking at your reply, and not in my SAS 9.4 when I do a PROC PRINT.

PROC Star
Posts: 736

Re: problem to convert a character into numeric with comma

Posted in reply to PaigeMiller

Ah, I mixed them up with periods, my bad Smiley Happy

SAS Super FREQ
Posts: 708

Re: problem to convert a character into numeric with comma

Posted in reply to Nasser_alfea

Your INPUT function with informat should just work fine, see example below:

 

data want;
  length amount $ 15;

  do amount = "82.04", "108498.19";
    amount_n = input(amount, 15.);
    output;
  end;
run;
Solution
‎06-07-2017 11:03 AM
Super User
Super User
Posts: 7,039

Re: problem to convert a character into numeric with comma

Posted in reply to Nasser_alfea

Are you reading from a text file? Make sure to use the TRUNCOVER option on the INFILE statement.  Or add the : modifier in front of the format in the INPUT statement.

Are you reading from a character variable using the INPUT() function? Is so then your issue is probably caused by non-printing characters on the end of the shorter string.  Like at TAB ('09'x) or a CR ('0D'x) or perhaps a NUL ('00'x) or what Microsoft likes to call a non-breaking space ('A0'x).  Try printing the value using $HEX format to see what characters are there and then use the COMPRESS() function to remove them.

If you want the value to print with commas as the thousand separators then use the COMMA format. If you want to change decimal point to a comma and use period as thousands separator then use COMMAX or change your locale settings.

 

data want ;
 set have ;
  amount_num = input(amount,32.);
  format amount_num comma14.2;
run;

 

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 290 views
  • 1 like
  • 5 in conversation