BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
marieK
Obsidian | Level 7

Hi,

 

I have  a SAS dataset with a text-variable (diags, 8000 characters) which I need to insert into an Oracle database table. Unfortunately the length of the variable is always truncated to 1024 characters, even with DBMAX_TEXT=8000 and CLOB datatype. I don´t get any error messages or warnings in the log. Do you have any ideas what could be the problem here? Thank you very much!

 

I use the following code with SAS 9.3:

 

libname _d2s_lib oracle user=xxx password="xxx" path=xxx schema=xxx DBMAX_TEXT=8000;

proc sql;
    connect to oracle (user=xxx password="xxx" path=phdb2b DBMAX_TEXT=8000);
        execute ( alter table xxx.test1 add
                ("IDNO" number, "SEX" number, "AGE" number, "DIAGS" CLOB) )
        by oracle;
    disconnect from oracle;
quit;

proc sql;
  insert into xxx.test1
    select * from work.test;
quit;

1 ACCEPTED SOLUTION

Accepted Solutions
marieK
Obsidian | Level 7

The solution is very simple 😉

It seems that the viewer to look at the Oracle datatables in SAS has a limit of 1024 characters for text variables in Oracle. As soon as I look at the data with another tool (sqldeveloper) I can see all values without truncation. If I read the Oracle dataset back into SAS with the DBMAX_TEXT=8000, I can also see the untruncated values in the work SAS dataset test3.

 

data test3;
    set xxx.test1(DBMAX_TEXT=8000);
run;

 

 

View solution in original post

5 REPLIES 5
kiranv_
Rhodochrosite | Level 12

have this options 

 

 options sastrace=",,,d" sastraceloc=saslog no$stsuffix;

 and try this 

 

proc sql;
  insert into xxx.test1 (DBMAX_TEXT=8000)
    select * from work.test;
quit;

marieK
Obsidian | Level 7

Thank you very much for your answer. Unfortunately it is not working out as well... I found out, that the variable is already 1024 characters long after I added it with the alter table add DIAGS CLOB. So even before I insert the values.

kiranv_
Rhodochrosite | Level 12

what you are saying indicates that it is getting assigned to default length that is $1024. I guess thhis is probably configuration level settings, which is overwriting your libname options, datset options. if possible, please contact your sas admin and discuss about his. I am very curious to know outcome of this.

ChrisBrooks
Ammonite | Level 13

It looks like the destination field is a CLOB (Character Large Object) and not a regular varchar. SAS doesn't have the concept of a CLOB so it looks like some unwanted conversion is going on. Many years ago I wrote a macro to store a SAS data set into an ORACLE BLOB (Binary Large Object) field that works for CLOBs as well. You run the macro, passing parameters which enable it to build all the files necessary to run the ORACLE Bulkload utility then shell out to the operating system and run the Bulkloader with the generated parameters.

 

Although I no longer have direct access to the code I wrote, a google search (and I'd completely forgotten this) showed me that I'd posted it as an answer to a question on another forum a long time ago - it's the third post on this page https://groups.google.com/forum/#!topic/comp.soft-sys.sas/nH9P9oATX3E so you could try adapting it for your purpose.

marieK
Obsidian | Level 7

The solution is very simple 😉

It seems that the viewer to look at the Oracle datatables in SAS has a limit of 1024 characters for text variables in Oracle. As soon as I look at the data with another tool (sqldeveloper) I can see all values without truncation. If I read the Oracle dataset back into SAS with the DBMAX_TEXT=8000, I can also see the untruncated values in the work SAS dataset test3.

 

data test3;
    set xxx.test1(DBMAX_TEXT=8000);
run;

 

 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 3461 views
  • 2 likes
  • 3 in conversation