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;

2 comments:

Anders Sk├Âllermo said...

Hi! According to GOOGLE the TO_NUMBER function is part of Oracle SQL. Perhaps it is available also n other dialects of SQL. Quite interesting.
/ Br Anders Skollermo

Rick Pack said...

I discovered that TO_NUMBER did not work in SAS 9.1.3.