SQL import from oracle is incorrectly importing a field that contains large integer values

[BRKS]

Hi all,

We have oracle databases that use very large integers as ID's as key fields used to join tables together (instead of treating them as characters, these integer values are stored as numbers that have up to 30 digits).

When I pull the data into SAS directly from oracle (using SAS EG v6.1 and proc sql), no matter what I have tried, these integers are not brought in correctly - they are transformed into floating point fields:

After pulling in 1.5 million records where I should have many thousands of unique numbers in this field.  When I run a proc freq on the field to create a table, there are only three unique values returned.  When I double click in the field that contains these 3 numbers, I see that it is a floating point representation of a number.

So I get why this is happening. - Somehow oracle is storing the numbers in a non-floating point field, and when SAS pulls it in, it is changing it to floating point.  

I'm a work at home employee, and am almost ready to give up and start running extracts from oracle to flat files on my laptop (across a cable connection) and then uploading those flat files to my SAS EG server (across the same cable connection), and importing those fields as characters into SAS EG.

I can't stand the idea of having to waste all of that time and processing just to pull data from Oracle into SAS.  Has anyone run inot this issue before and found a way to prevent it?  

Thanks so much,

Barb

[Tom]

The best way to prevent it is to have the Oracle team not use insanely large integer values for ID variables. No one ever needs to know the average of an ID variable so they should be created as character strings to begin with.

If you cannot get them to fix their systems then look at using the DBSASTYPE option.

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0v4ma1zb9lu99n172...

Tell SAS to create the variables as character.

data extract;
  set oralib.mytable(dbsastype=(ID='CHAR(30)'));
run;

[BRKS]

Thank you Tom!! I think it will be unlikely for me to get the fields changed in oracle, so I appreciate your advice!! I will use this the next time that I use a SAS data step! Is there a way to do something similar in SAS sql?

[Tom]

---

@BRKS wrote:
I will use this the next time that I use a SAS data step! Is there a way to do something similar in SAS sql?

---

The way you reference datasets in SAS SQL statements is no different than in DATA step statements. So just use the same dataset option in your SQL code.

create table want as
select * 
from myoralib.have (dbsastype=(id=char(30)))
;

If you can make direct connection to the databas in PROC SQL then you could push the conversion into the database instead using passthru SQL.  That way you can use the SQL dialect of the remote database to make the transformation.

proc sql noprint ;
  connect to oracle .... ;
  create table want as 
    select * from connection to oracle
(select cast(id as varchar(30)) as id,var2,var3,....
  from myschema.mytable
)
  ;

Or even better have the database team make some SAS compatible views that convert the fields that use data types that are incompaitble with SAS to values that are usable by SAS.  You might have other large integer variables or BLOB or CLOB variables that could be split into separate strings.

They could also fix other issues that could make their database difficult to use from SAS at the same time.

• Rename long variable names or long table names to be 32 characters or less
• Convert sequences of similarly named variables to have the sequence number at the end of the name instead of the middle
  • person1_name as person_name1

[BRKS]

Hi Tom,

Thank you so much.  I'm a business analyst who does database manipulation when I have to, so I'm not very good at proc SQL, so I seem to have not understood how to do this properly.  Do you think you can offer insite into what I'm doing wrong?

Thank you so much for your help!!  

Barb

Here is my program:

proc sql;

create table op_est as

(SELECT cast(e.affltn_srvc_prvdr_id as varchar(30)),
        p.prvdr_idntfr as pid_doc,
        p.prvdr_tin as tin_doc,
        e.srvc_code,
        e.srvc_estmt_ctgry_code,
        e.allwd_srvc_estmt_amnt as est_total_amt

 FROM  DRCTRY_C.ds_srvc_prvdr p,
       DRCTRY_C.ds_srvc_estmt e

WHERE p.srvc_prvdr_id = e.srvc_prvdr_id
  AND e.allwd_srvc_estmt_amnt > 0
  AND e.srvc_code in ('45380', '45378', '43239', '45385', '66984', '29881', '29826', '95810', '58558', '30520', '29888')
)
;
quit;

Here is the log with the error message that I'm getting:

24     proc sql;
25          create table op_est as
26
27          (SELECT cast(e.affltn_srvc_prvdr_id as varchar(30)),
                                                __
                                                22
                                                202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

28                    p.prvdr_idntfr as pid_doc,
29                    p.prvdr_tin as tin_doc,
30                    e.srvc_code,
31                    e.srvc_estmt_ctgry_code,
32                    e.allwd_srvc_estmt_amnt as est_total_amt
33
34             FROM DRCTRY_C.ds_srvc_prvdr p,
35                  DRCTRY_C.ds_srvc_estmt e
36
37            WHERE p.srvc_prvdr_id = e.srvc_prvdr_id
38              AND e.allwd_srvc_estmt_amnt > 0
39              AND e.srvc_code in ('45380', '45378', '43239', '45385', '66984', '29881', '29826', '95810', '58558', '30520',
39    ! '29888')
40           )
41           ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
42 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds

[Tom · Accepted solution]

Your query is totally coded in SAS, you have no pass-thru query to the database. SAS does not have a CAST() function.

So instead you need to use the dataset options to tell SAS how to transfer the data from the database to SAS.

proc sql;
create table op_est as
  SELECT e.affltn_srvc_prvdr_id
       , p.prvdr_idntfr as pid_doc
       , p.prvdr_tin as tin_doc
       , e.srvc_code
       , e.srvc_estmt_ctgry_code
       , e.allwd_srvc_estmt_amnt as est_total_amt
  FROM DRCTRY_C.ds_srvc_prvdr p
     , DRCTRY_C.ds_srvc_estmt(dbsastype=(affltn_srvc_prvdr_id='CHAR(30)')) e
  WHERE p.srvc_prvdr_id = e.srvc_prvdr_id
    AND e.allwd_srvc_estmt_amnt > 0
    AND e.srvc_code in
          ('45380', '45378', '43239', '45385', '66984', '29881'
          , '29826', '95810', '58558', '30520', '29888')
;
quit;

[BRKS]

Thank you Tom!!!!!!!

I really appreciate your patience and persistence with this issue!

That worked like a charm and I am all set now.

I really appreciate your advice and willingness to "dummy it down" for me.  🙂

Barb

P.S.  I see that you put the commas before each additional field name - what a great idea.  I'm always getting dinged by SAS for forgetting to include a comma or for putting one in where it shouldn't be.  I'm going to pick up that practice myself.

[Patrick]

@BRKS

A SAS numeric variable uses 8Bytes and though can only store up to 15 digits with full precision. 

Using the DBSASTYPE option in the way Tom suggest allows you to cast the values to a character string. That should work.

If you have other cases where you need even more control then you can always use pass-through SQL to the database where you first cast()/transform your variables to whatever you need before you download them into SAS.

[BRKS]

Thank you Patrick! My thought is that I would need read/write access in order to do the SQL pass-through step that you are suggesting. Unfortunately I only have read access. Do you have any ideas for how I could make this work in my read-only status?

[SASKiwi]

You can do pass-through with read only access as you are not updating any Oracle tables just running a select-type query.

[kiranv_]

Got into the same issue four years back and did what @Tom suggested, which fixed the issue and reason is what @Patrick mentioned.