topic Re: Converting character format to numeric format in SAS Procedures

Converting character format to numeric format

Jordan88 — Mon, 05 Oct 2015 17:40:11 GMT

Hello,

I am brand new to SAS (am more expierenced with STATA) and am having difficulty converting character values to numeric values.

I am doing survival analysis, and have a column of "time_to_event" that is in days. When I import the data to SAS from an excel sheet, all variables important in character format, which prevents the survival analysis from working.

I have tried the following:

Data practice;

set work.import1;

format tte BEST6.;

tte=input(time_to_event, $5.);

run;

It generates a new SAS table, but I get an error for the conversion. The log shows the following :

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

59:6

NOTE: Invalid numeric data, '.617' , at line 59 column 6
time_to_event=617 status=1 location=!!!! sex=Male age_group=<=20 region=Quebec histology=Diffuse astrocytoma tte=. _ERROR_=1 _N_=1"

Does anyone have any ideas how to fix this, or know a better way of going about it? There is a forum thread from 2012 about doing thsi conversion, but the all of the provided solutions produce the same error.

Re: Converting character format to numeric format

Astounding — Mon, 05 Oct 2015 17:49:16 GMT

You're very close ... just get rid of the dollar sign: tte = input(time_to_event, 5.);

Re: Converting character format to numeric format

Jordan88 — Mon, 05 Oct 2015 18:03:47 GMT

Hmm... I tried that, and it it ends up with the same result but a different error:

"NOTE: Invalid argument to function INPUT at line 59 column 6.

time_to_event=617 status=1 location=North sex=Male age_group=<=20 region=Quebec histology=Diffuse astrocytoma tte=. _ERROR_=1 _N_=1"

Re: Converting character format to numeric format

SASKiwi — Mon, 05 Oct 2015 18:35:18 GMT

Check the variable type of time_to_event. Your error suggests that it may be numeric already.

Re: Converting character format to numeric format

Astounding — Mon, 05 Oct 2015 18:38:42 GMT

Two "next steps" then to help diagnose the problem ... First, show the area of the log that contains line 59. It seems unusual that column 6 would begin the invalid value that is mentioned by the error message. Second, assuming that time_to_event is actually a character field, for _n_=1 display time_to_event in two forms just to confirm what it contains: put time_to_event time_to_event $hex8.;

Re: Converting character format to numeric format

Jordan88 — Mon, 05 Oct 2015 20:30:24 GMT

I have attached a screenshot of the error. I also don't really know why is referring to column 7, as column 7 is histology and time_to_event is column 1.

To answer the other person's comment, the properties show time_to_event being:

Type: Char

Format: $5.
Informat: $5.

For the put time_to_event time_to_event $hex8.; I'm not sure where you want me to put this. Running it on its own returns "ERROR 180-322: Statement is not valid or it is used out of proper order."

Re: Converting character format to numeric format

MikeZdeb — Mon, 05 Oct 2015 20:32:47 GMT

Hi. It's good to know the variable type before using functions. But if you don't care, you could use a CAT function with that INPUT function and SAS would give the same result whether the variable is CHAR or NUM ...

data _null_;
format num char 10.2;
retain x '12345' y 12345;
char = input(cat(x),5.);
num = input(cat(y),5.);
put x= char= / y= num=;
run;

the LOG .(no "nasty" messages) ...

191 data _null_;
192 format num char 10.2;
193 retain x '12345' y 12345;
194 char = input(cat(x),5.);
195 num = input(cat(y),5.);
196 put x= char= / y= num=;
197 run;

x=12345 char=12345.00
y=12345 num=12345.00

Re: Converting character format to numeric format

Astounding — Mon, 05 Oct 2015 20:44:33 GMT

The hex format will help determine if the decimal point we are seeing is not really a decimal point but some other unprintable character. Let's see the results you get in this DATA step ("have" should be your incoming SAS data set name): data _null_; set have; if _n_=1 then put time_to_event time_to_event $hex10.; else stop; run;

Re: Converting character format to numeric format

Jordan88 — Mon, 05 Oct 2015 20:56:14 GMT

Here are the results of that data step.

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

56 data _null_;

57 set import;

58 if _n_=1 then put time_to_event time_to_event $hex10.;

59 else stop;

60 run;

617 0A36313720

NOTE: There were 2 observations read from the data set WORK.IMPORT.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

62 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: Converting character format to numeric format

Astounding — Mon, 05 Oct 2015 21:10:10 GMT

OK, that narrows down the scope of the problem considerably. The "." in ".617" is not a decimal point, but is some other character. The next question becomes, "How would you like to handle it?" Should all the numeric values ignore the "." or should the "." be treated as if it were a decimal point? (It might help simplify the solution if we knew whether the "." always appears at the beginning of each time_to_event, but we can code around it if the "." is only there occasionally.)

Re: Converting character format to numeric format

Jordan88 — Mon, 05 Oct 2015 21:24:24 GMT

Interesting, I wonder why that is happening as there doesn't appear to be anything when looking at the table or the orginal excel.

But to answer your question, I do not want any decimal point as all values should be in whole days. I don't know if it is there for all values, as the log only shows the first 20 attempts and there are no decimals in the table (there are 10,000 entries), but all 20 of them are shown in the log have the decimal.

Re: Converting character format to numeric format

ballardw — Mon, 05 Oct 2015 21:56:24 GMT

@Jordan88 wrote:

I have attached a screenshot of the error. I also don't really know why is referring to column 7, as column 7 is histology and time_to_event is column 1.

The error message is referring to the position within the line of code. In this case likely the position of the start of the Input function. It points to the first place in a line that the operation cannont be completed due to any error.

If the data actually contains the quotes that is another reason for the conversion to fail. That is why the error shows invalid numeric value '.615'

Re: Converting character format to numeric format

Jordan88 — Mon, 05 Oct 2015 22:10:11 GMT

Oh ok, that makes sense, thank you.

And the data itself does not contain the quotes. I have attached a screenshot of the data.

Re: Converting character format to numeric format

Astounding — Mon, 05 Oct 2015 22:57:32 GMT

OK, here is what I would try. In the context of a DATA step:

data want;

set have;

tte = input(compress(time_to_event, '0A'x), 5.);

run;

That should remove the extra character, then convert what is left to numeric.

Good luck!

Re: Converting character format to numeric format

Jordan88 — Tue, 06 Oct 2015 03:03:55 GMT

Yes, thank you!

I still don't know why there was some invisible character, but this solved it. Thanks for all your help.

Just for the sake of learning, what does the '0A'x mean/do?

Re: Converting character format to numeric format

Astounding — Tue, 06 Oct 2015 13:21:31 GMT

Every character can be represented by a two-character hexadecimal code. Your earlier experiment wrote out time_to_event in hexadecimal form, and found that the "." could be represented the hexadecimal code 0A. So we're using that information to remove the "." with the COMPRESS function.