DATA Step, Macro, Functions and more

About convert variable's type

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

About convert variable's type

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:

QQ截图20140513104222.png

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:

QQ图片20140513105137.jpg

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:

QQ图片20140513110258.jpg

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.

Attachment

Accepted Solutions
Solution
‎05-13-2014 01:00 AM
Trusted Advisor
Posts: 1,131

Re: About convert variable's type

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

Thanks,
Jag

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

Re: About convert variable's type

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;

Contributor
Posts: 65

Re: About convert variable's type

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.

QQ截图20140513132742.png

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!

Solution
‎05-13-2014 01:00 AM
Trusted Advisor
Posts: 1,131

Re: About convert variable's type

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

Thanks,
Jag
Contributor
Posts: 65

Re: About convert variable's type

Thanks very much! That can fix this problem.Smiley Wink

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;

QQ截图20140513131738.png

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 233 views
  • 4 likes
  • 3 in conversation