topic Re: Convert a character values with comma to numeric in SAS Programming

Convert a character values with comma to numeric

SASdevAnneMarie — Fri, 09 Apr 2021 15:03:12 GMT

Hello Experts,

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

Thank you for help !

Re: Convert a character values with comma to numeric

sbxkoenk — Fri, 09 Apr 2021 15:07:10 GMT

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

Re: Convert a character values with comma to numeric

sbxkoenk — Fri, 09 Apr 2021 15:12:00 GMT

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

Re: Convert a character values with comma to numeric

sbxkoenk — Fri, 09 Apr 2021 15:17:03 GMT

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

Good luck,

Koen

Re: Convert a character values with comma to numeric

SASdevAnneMarie — Fri, 09 Apr 2021 15:17:11 GMT

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

Re: Convert a character values with comma to numeric

SASdevAnneMarie — Fri, 09 Apr 2021 15:17:53 GMT

Thank you, Koen, I noted ! 🙂

Re: Convert a character values with comma to numeric

sbxkoenk — Fri, 09 Apr 2021 15:25:52 GMT

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

Re: Convert a character values with comma to numeric

SASdevAnneMarie — Fri, 09 Apr 2021 15:33:48 GMT

Thank you very much Koen !

Re: Convert a character values with comma to numeric

Tom — Fri, 09 Apr 2021 16:13:50 GMT

@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

Re: Convert a character values with comma to numeric

sbxkoenk — Fri, 09 Apr 2021 16:19:08 GMT

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

Re: Convert a character values with comma to numeric

Tom — Fri, 09 Apr 2021 16:28:24 GMT

@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).

Re: Convert a character values with comma to numeric

Ksharp — Sat, 10 Apr 2021 10:29:25 GMT

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