Convert a character values with comma to numeric

[SASdevAnneMarie]

Hello Experts,

Do you know please how to convert this column to numeric without passing by "substr" function ?

Thank you for help !

[sbxkoenk · Accepted solution]

data have;
 CommaNumberChar = '76087,8';
run;
data want;
 set have;
 Number=input(CommaNumberChar, commax10.1);
run;
/* end of program */

[sbxkoenk]

Or use the bestw. informat (best12., best32., ...).

Good luck,

Koen

[Tom]

---

@sbxkoenk wrote:

Or use the bestw. informat (best12., best32., ...).

Good luck,

Koen

---

Note that there is not really a BEST informat.  There is a BEST format.

If you use BEST as in informat then SAS will just use the regular numeric informat.  So writing BEST12. informat is just a longer (more confusing) way of asking for the 12. informat

In any case this will not normally support of using comma as the decimal separator instead of period.  You probably need use on of the informats with X at the end.  Like COMMA or COMMAX. or NLNUM or NLNUMI.

https://documentation.sas.com/api/docsets/nlsref/1.0/content/nlsref.pdf?locale=en

[sbxkoenk]

Hello @Tom ,

You are absolutely right!

I was misled by the data step generated by proc import. It sometimes uses best32. as an INformat.

For @SASdevAnneMarie something like nlnum32. would also work as an informat (even when commas are there).

And nlnum32. is more "generic" than commax10.1.

Thanks for putting the finishing touches to this.

Koen

[Tom]

---

@sbxkoenk wrote:

Hello @Tom ,

You are absolutely right!

I was misled by the data step generated by proc import. It sometimes uses best32. as an INformat.

For @SASdevAnneMarie something like nlnum32. would also work as an informat (even when commas are there).

And nlnum32. is more "generic" than commax10.1.

Thanks for putting the finishing touches to this.

Koen

---

I only use the code generated by PROC IMPORT to get an idea of how it guessed to defined the variables (type and length) and to see if any variables have informats or formats that are needed (like DATE or MMDDYY).  In general many tools like that which need to generate code will produce code that is too verbose.   But I am not sure why PROC IMPORT is writing code that is using the side effect of attaching an INFORMAT to the variable as the first place the variable appears to force SAS to define the type and length of the variables.  It should just generate a LENGTH or ATTRIB statement.  Then it would only need to attach informats to the variables that need them (which is not many).

[SASdevAnneMarie]

Thank you !
Do you know please the monetary format like 22,5 € ? I can't find it.

[sbxkoenk]

Hello,

Sorry, the BESTw. informat does not work for numbers with commas (when the numbers have the character data type).

On the topic of: format like 22,5 € ?

It's the EUROw.d Format or the EUROXw.d Format (with commas).

data have;
 CommaNumberChar = '11111,22';
run;
data want;
 set have;
 Number1=input(CommaNumberChar, commax12.2);
 Number2=Number1;
 format Number1 EURO12.2;
 format Number2 EUROX12.2;
run;
/* end of program */

Cheers,

Koen

[SASdevAnneMarie]

Thank you very much Koen !

[sbxkoenk]

On top of my previous response ...

It's better to provide us with your data by using a data-step with datalines (cards) statement instead of a screenshot. We can immediately use these data then by copying / pasting in our own SAS session.

Important: If you copy / paste your SAS program in your question-entry, be sure to use the 'Insert SAS Code' icon. That way structure and formatting is preserved and some useful highlighting and coloring is done on the statements.

Cheers,

Koen

[SASdevAnneMarie]

Thank you, Koen, I noted ! 🙂

[Ksharp]

data have;
 CommaNumberChar = '76087,8';
run;
data want;
 set have;
 Number=input(CommaNumberChar, numx20.);
run;