preserve trailing zero when converting from character to num

[jojozheng]

Hi everyone, 

I have a column of character like: 

flash

1.04

2.11

0.7

1

1.1

2.04

I want them to convert to numeric so I can use mean function.

flash 

1.04

2.11

0.70

1.00

1.10

2.04

I tried input(flash, best.), all value are missing... also tried input(flash,best4.2), it becomes

1.04

2.11

.

.

.

2.04

I cannot figure out how to do this...BTW, how can I create another numeric column with the same value? Can anyone provide some detail codes? Thank you so much!  

[VDD]

data have;
input mydata $;
cards;
1.04
2.11
0.7
1
1.1
2.04
;
data want;
 length mynum 8;
 set have;
 mynum = mydata;
 format mynum 8.2;
run;

[VDD]

the length statement creates a new numeric variable

the format statement applies the 8.2 value to the number variable. 

[ChrisNZ]

@VDD

Your code generates the message

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

While the informat best. is sometimes disappointing, it is best practice to use it (or another format, I usually use dollar32.) and to avoid such messages.

[jojozheng]

Thank you@VDD
I tried your method, but it failed.

[ChrisNZ]

This works.

data HAVE;
  input FLASH $;
cards;
1.04
2.11
0.7
1
1.1
2.04
run;


data WANT;
  set HAVE;
  F=input(FLASH,best.);
  drop FLASH;
  rename F=FLASH;
run;

I suspect your getting missing values means your data is not what you describe.

[jojozheng]

Thank you @ChrisNZ but is doesn't work well for my case

[Ksharp]

Maybe you have some blanks at beginning of FLASH.

F=input(STRIP(FLASH),best32.);

[jojozheng]

Thank you all! but answers you guys provided are not  work for my situation. Flash is a character with 32 length. I also tried

to convert it to num like flash1=flash*1, but only a part of dataset change to num type, others are missing. and I got note as below.

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

75:13

NOTE: Invalid numeric data, FLASH='1.04.'

why this happen? I feel very confused. Which means this flash column is not the same type of variable at beginning, so that's why some of flash can change to num, while those situation like above can't change. Also, in my output I only see 1.04, why log note display 1.04.

Any thought will be grateful! Thank you so much for your help!

[Doc_Duke · Accepted solution]

JoJo,

This looks like you have some unprintable characters in the flash column.  Use a text editor that is capable of displaying the hex coding of the data to see if that is the case.  If you don't have one, you could also use SAS to print the text field with a HEX format.

[jojozheng]

Thank you @Doc_Duke You remind me about unprintable data. So I search online and I found a article is really useful! I want to share with all of you~

https://www.lexjansen.com/pharmasug/2010/CC/CC13.pdf

And thank you all! I appreciate your help!

[Tom]

So if your variable is character then you can use the INPUT() function to convert it to a number, But you need to store the number in a new variable, since your existing variable is character.

data have ;
  input flash $32. ;
cards;
1.04
2.11
0.7
1
1.1
2.04
;

data want ;
  set have ;
  flash_num = input(flash,32.);
  format flash_num 8.2 ;
run;

Do not use too short of width on your informat.  It might be that the values having leading spaces that you don't see when you look at the output.  Do not use a decimal part on an informat. That tells SAS to divide any text values that do not have an explicit decimal point by that power of ten.

If you are still having trouble with the INPUT() function not generating valid numbers then check what is really in your character variable.  You could print the values using the $HEX format can check if it contains invisible characters like Carriage Return (0D), Line Feed (0A), Tab (09), Non-Breaking Space (A0), Null (00).  If so then remove those.

  flash_num = input(compress(flash,,'kdp'),32.);