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;

Create Oracle Tables

libname oradb Oracle User=orauser Password=xxxxxxx Path="@orapth";

proc sql;
      create table oradb.dsn as
      select * from temp;
quit;
run;

proc append base=oradb.dsn data=temp1 force;
run;

Generate RTF, XML, PDF, and Excel files with ODS and SAS/IntrNet

SAS IntrNet

Combining Queries with Set Operators

Link: UNION, EXCEPT, INTERSECT, OUTER UNION, etc.

Phonetic Matching (=*) & Pattern Matching (% and _)

Phonetic matching (Sounds-Like Operator =*)
e.g.
where custname =* 'Lafler';

Finding patterns in a string (Pattern matching % and _)
e.g.
where prodtype like '%soft%';
where prodtype like '____a%';

LIKE Clause in CREATE TABLE

The LIKE clause tiggers the existing table's structure to be copied to the new table minus any column dropped with the KEEP= or DROP= option.

proc sql;
      create table hot_products
      like products;
quit;

LOG Results:
NOTE: Table HOT_PRODUCTS created, with 0 rows and 5 columns.

Case Logic Versus COALESCE Expression

PROC SQL;
      SELECT CUSTNAME,
        CASE
          WHEN CUSTCITY IS NOT NULL THEN CUSTCITY
          ELSE 'Unknown'
        END AS Customer_City
      FROM CUSTOMERS;
QUIT;

PROC SQL;
      SELECT CUSTNAME,
        COALESCE(CUSTCITY, 'Unknown')
        AS Customer_City
      FROM CUSTOMERS;
QUIT;

ANSI Standard

The ANSI standard reserves a number of SQL keywords from being used as column names. If a column name conflicts with a reserved word,

PROC SQL DQUOTE=ANSI;
      SELECT *
      FROM RESERVED_WORDS
      WHERE "WHERE"='EXAMPLE";
QUIT;