topic proc sql - odbc - convert numeric to character in SAS Health and Life Sciences

proc sql - odbc - convert numeric to character

saslover — Wed, 22 Jun 2011 19:53:53 GMT

I have an issue.. I am using the ODBC with proc sql. This is my code
proc sql noprint;
connect to odbc
as atest (noprompt="DRIVER=Microsoft Excel Driver (*.xls);
DBQ=xxxxx.xls;");
create table day4 as
select * from connection to atest
(
select * from "abc tensile test 6-2-11.is$"
);
disconnect from atest;
quit;

Now the excel file reads in, but in my original data file I have some values for id that have 201 202 203 R201 R202. So SAS gives me missing values for all the character variables but reads in only numeric.

How do I define conversion of numeric to character variable inside my data step above in proc sql.

Any help is appreciated

Thanks

Re: proc sql - odbc - convert numeric to character

Reeza — Wed, 22 Jun 2011 20:45:17 GMT

That seems cumbersome.

I suggest using proc import with type = mixed.

Re: proc sql - odbc - convert numeric to character

saslover — Wed, 22 Jun 2011 20:48:09 GMT

I cannot do that. My dataset has a whole lot of junk characters in the sheets and they are not similar across all excel files. So PROC IMPORT doesn't work.

Re: proc sql - odbc - convert numeric to character

Reeza — Wed, 22 Jun 2011 22:58:03 GMT

I think the dbtype= option might be what you want then.

I don't know exactly wwere/how but hopefully google can help some more.