data type changed from SAS to Oracle

[learner_sas]

Hello Everybody,

I used SAS to generate table  which is ultimately posted in Oracle. Today I found out that data type of some of my variables has been changed in table descriptions

For example : Variable Sales is numeric inside SAS table but  in oracle  description of sales comes as VARCHAR2(20). but when you try to

use select * from table_oracle where sales>'2000' it does not work but select * from table_oracle where sales>2000(numeric) works.

I made my audience confuse since it says one thing in table description and other thing in query.

I am wondering if any of you know how to fix it.

[jakarman]

See: SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition (datatypes for oracle)

Indeed datatypes are different between some systems and working with numbers is confusing.

For fun  and example: the International Bank Account Number - Wikipedia, the free encyclopedia is containing letters. Enven recognizing the digits and letters can become a farce.

Back to Oracle and SAS. DBTYPE and DBSAStype will give you more control on the conversion. But converion and convertions are needed understanding.

For example many Oracle-DBA are indicating every field containing the characters 0-9 numeric. This better described as character fields having the restriction of using some of the limites characters. Binary an Floating and packed types are the real numeric types. The inheritance of hollerith cards is still misunderstood. 

There is a new langauge type DS2 that relies more on common DBMS types.

[Patrick]

To add to Jaap's post:

Use "OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;" in your code. This will show you in your log what SQL gets actually sent to the DB.

It's also always a good idea to use SQL Pass-through for creating tables as this gives you full control of how the table gets created and you don't rely on the SAS/Access engine to do the work for you as the code the access engine will generate depends on settings for DBTYPE and DBSAStype. Most of the time you get a "correct" SQL Create statement - but using Pass-Through is better.

Actually: In almost all bigger projects I've been working so far we used SQL Developer to create tables in Oracle in a DEV environment. We then normally handed over the DDL to a Oracle DBA for verification and tweaking and it is this DDL which gets executed when migrating to higher environments (TEST, UAT,... PROD).