Tuesday, November 19, 2013

Convert CHAR to NUM in PROC SQL

Use TO_NUMBER function in PROC SQL.

proc sql;
   connect to oracle (user=xxx orapw=yyy path="@zzz");
     create table temp as
       select * from connection to oracle
              (select aa, to_number(bb) as bb, to_number(cc) as cc
                          from xxx.dddd)
       where bb ne . or cc ne .
       order by aa;
   disconnect from oracle;
quit;
run;