topic Re: preserve trailing zero when converting from character to num in SAS Programming

preserve trailing zero when converting from character to num

jojozheng — Thu, 13 Sep 2018 01:46:00 GMT

Hi everyone,

I have a column of character like:

flash

1.04

2.11

0.7

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!

Re: preserve trailing zero when converting from character to num

VDD — Thu, 13 Sep 2018 02:01:40 GMT

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;

Re: preserve trailing zero when converting from character to num

ChrisNZ — Thu, 13 Sep 2018 02:11:24 GMT

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.

Re: preserve trailing zero when converting from character to num

VDD — Thu, 13 Sep 2018 02:07:55 GMT

the length statement creates a new numeric variable

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

Re: preserve trailing zero when converting from character to num

ChrisNZ — Thu, 13 Sep 2018 02:15:26 GMT

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

Re: preserve trailing zero when converting from character to num

Ksharp — Thu, 13 Sep 2018 13:02:25 GMT

Maybe you have some blanks at beginning of FLASH.

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

Re: preserve trailing zero when converting from character to num

jojozheng — Thu, 13 Sep 2018 13:38:16 GMT

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!

Re: preserve trailing zero when converting from character to num

jojozheng — Thu, 13 Sep 2018 13:39:16 GMT

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

Re: preserve trailing zero when converting from character to num

jojozheng — Thu, 13 Sep 2018 13:40:35 GMT

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

Re: preserve trailing zero when converting from character to num

Doc_Duke — Thu, 13 Sep 2018 13:47:17 GMT

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.

Re: preserve trailing zero when converting from character to num

Tom — Thu, 13 Sep 2018 14:06:53 GMT

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

Re: preserve trailing zero when converting from character to num

jojozheng — Thu, 13 Sep 2018 14:40:20 GMT

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!