How to Read Comma-Embedded Numbers into Numeric Variable?

[deleted_user]

Hi, I just run into another problem about data conversion -

I want to create a new numeric variable VAR_NEW to equal an existing SAS variable VAR_OLD. However, the numbers contained in VAR_OLD have embedded commas, such as "1,026", "3,085", which makes it essentially in a string format aligned in the middle of the variable column.

As a result, the value of VAR_NEW becomes missing whenever a VAR_OLD value is greater than 999. So, what is the easy way to generate VAR_NEW without the missing value issue?

Thanks a lot!

Message was edited by: SASLooker Message was edited by: SASLooker

[GertNissen]

If I understand your question correct - try

data test;
set data;
format var_new 8.;
var_new = input(var_old,best.);
run;

[deleted_user]

Hi Geniz,

Thanks for the suggestion. I tried that, but that makes var_new entirely into missing values.

But following your direction, I came across this, and it worked perfectly -

data test;
set data;
var_new = input(var_old, comma8.);
run;

Although the result is satisfactory, I am not so sure if using COMMAw.d this way is technically correct, or I just got lucky. Because the doumentation I found about COMMAw.d typically refers to reading external file rather than to converting a SAS internal data file. So, I'd like to hear the others' confirmation or verification.

[Patrick]

The COMMAw.d and other informats tell SAS how to interprete a string for converting it into a SAS number. This string can of course be stored in a SAS character variable.

[SuperSas1]

If you have a comma in a text field using the comma8. input statement works; the best will not format it correctly and continue with the same problem.  

[Tom]

---

@SuperSas1 wrote:

If you have a comma in a text field using the comma8. input statement works; the best will not format it correctly and continue with the same problem.  

---

Not sure why you are responding to an ancient thread....

The INPUT() function does not mind if the width used on the informat is larger than the length of the string being read, so in general you should just use the maximum width that the informat supports.  For COMMA that is 32.

number = input(string,comma32.);

Also notice that do not want to include a decimal width on an informat except for the rare occasion when you know the strings being read were created explicitly without the decimal point and want SAS to divide by that power of 10 to insert one.  This is normally not done for strings that include commas.

Note also the BEST is the name of a FORMAT.  Its name comes from its attempt to find the "best" way to display a number in a limited number of characters.  There is no corresponding concept of the "best" way to store a string as a number.  Numbers in SAS can only be stored one way, as 64 bit binary floating point values.  If you use BEST as an informat then SAS just assumes you meant to use the normal numeric informat, which does not work with strings that contain commas.

[Liz_LSU]

SASLooker,

It's not clear from your message where VAR_OLD comes from. Are you reading it in as a character variable? If so, then read it as a numeric variable using the COMMAw.d informat, as Patrick suggested.

In your INPUT statement, the code

VAR_OLD :COMMA5.

will read anything up to four digits, with or without a comma, as a numeric variable. The colon modifier tells SAS to ignore the width (5) and lets you read anything from one- to four-digit numbers. If you don't include the colon modifier and there are numbers less than 1,000, you won't get the expected results for those numbers.

If it's in a permanent SAS data set rather than one you are creating when you run your program, never mind!

liz

[deleted_user]

thanks for the tip, liz! I am dealing with a permanent SAS data, and for now, everything seems to work out. But I'll bear your suggestion in mind if I deal with other situations.