About convert variable's type

[Slash]

Hi, guys!

   

     I got a problem, find the reason, but do not know how to fix it. Here it is:

The sas data is read by using IMPORT procedure. Here is the data look like:

Notice that the last variable GPRS_FLUX is character. So I want to convert it to numeric. I use these code:

data work.test;
  set data.sas_info_20140512173730(rename=(gprs_flux=tp_0));
  gprs_flux=input(tp_0,best12.);
  drop tp_:;
run;

Unfortunately, the log told me that the convertion is failed!!! Here is the Log:

It said the argument of the INPUT function is invalid at row 20, col 12. I really cannot find which is invalid. It tortured me half an hour.

Finally I export the data to TXT file, I realize the problem. Here is the exported text file:

It got a quotation marks! How does this happen???? Why I can't see it in EG????

So the problem is caused by the quotation marks, so how to convert it to numeric?

Note: the data is in the remote SAS server,  so don't tell me to read the data again. Because there are lots of data is automatically read, and the data's column is not same at each time, so must use the IMPORT procedure.

[stat_sas]

Not sure about the data type when you imported but you can use dequote to get rid of quotation marks.

data work.test;

  set data.sas_info_20140512173730(rename=(gprs_flux=tp_0));

  gprs_flux=input(dequote(tp_0),best12.);

  drop tp_:;

run;

[Slash]

Thanks! It's my fault, not the quotation marks' problem. The data contain a RETURN Key, that's the key.

I just didn't find out, when I use notepad++ open the exported data, it looks like this, different from the notepad.

And I use $hex. format to put the data, and there is a RETURN key, check out my another comment.

Thank you all the same!

[Jagadishkatam · Accepted solution]

Please try the below code  

data work.test;    

set data.sas_info_20140512173730(rename=(gprs_flux=tp_0));    

gprs_flux=input(compress(tp_0,,'kw'),best12.);     

drop tp_:;  

run;

I used the compress function to remove the non printable characters. There are some hidden non printable characters in the variable imported. Due to which the character data is not converted to numeric.

Note: KW in compress stands for keep writable.

Hope this helps.

Thanks,

Jag

[Slash]

Thanks very much! That can fix this problem.

And I learn the another usage of compress function. Thanks again!

And I also find the character which cause the problem, it's not the quotation marks. It's the RETURN key.

I use $hex. format to put the variable's value, find the '0d'x character. Here it is:

data test;

  set data.bqc_to_sas_info_20140512173730(obs=10);

  tp=put(gprs_flux,$hex.);

run;