BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Cugel
Obsidian | Level 7
I'm looking for a way to read a JSON column from an Oracle table into SAS.
The Oracle table contains a column called "Document" in which a JSON structure is stored.
Now our organization uses an ODBC link to Oracle but the ODBC link has a 1024 limitation on the number of characters.
The "Document" column contains 32767 characters broken down into a large number of JSON arrays.
Is there a possibility to access this Oracle table directly on the Oracle server and then read it into SAS tables?.
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
Try libname 's option DBMAX_TEXT= .

libname x odbc dsn=xxx dbmax_text=32767 ;

View solution in original post

3 REPLIES 3
Ksharp
Super User
Try libname 's option DBMAX_TEXT= .

libname x odbc dsn=xxx dbmax_text=32767 ;
Cugel
Obsidian | Level 7
I will discuss with our technical colleagues whether this is the solution to our problem.
Tom
Super User Tom
Super User

In general no.  Unless you want to write your own logic to scan the text and figure out what parts are the data and what parts and the variable names.

 

But you could perhaps write the data into an actual FILE and then try using the JSON libref engine and see if SAS can parse the JSON into something that at least looks a little like data.

 

Make sure to add DBMAX_TEXT=32767 dataset option so you can pull out all of the text.

 

You might also need add some extra text to the generated file so that the result is valid JSON syntax.  If nothing else perhaps opening and closing square brackets and commas between the lines.

 

Try something like:

filename json temp;
data _null_;
  set mylib.mydata(dbmax_text=32767) end=eof;
  if _n_=1 then put '[' @;
  else put ',' @;
  put json_text ;
  if eof then put ']';
run;
libname json json ;
proc copy inlib=json out=work;
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 536 views
  • 4 likes
  • 3 in conversation