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?.
I will discuss with our technical colleagues whether this is the solution to our problem.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: