BookmarkSubscribeRSS Feed
learner_sas
Quartz | Level 8

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.

2 REPLIES 2
jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
Patrick
Opal | Level 21

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).

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 852 views
  • 0 likes
  • 3 in conversation