I have a table with a column(JSONDOC) which has the dataset as shown in the picture below.
I need to split the contents of this column into multiple columns (Kindly see the highlighted parts of the column which will serve as the new column names).
I have seen quite a number of solutions using proc SQL on here, but will like to know how I can implement this using the SAS DI Studio.
You may want to check the LIBNAME statement, JSON engine documentation as it appears that you may have a JSON file.
Let me give you a clearer background on this. The table is an Oracle table, and I loaded this into SAS using the SAS Data Integration Studio. I will be loading it into SQL Server but need to split this column into different columns.
@Soulbroda wrote:
Let me give you a clearer background on this. The table is an Oracle table, and I loaded this into SAS using the SAS Data Integration Studio. I will be loading it into SQL Server but need to split this column into different columns.
Can you have the Oracle team build you a view that parses out the values you need using the Oracle functions for parsing JSON text? That would be easiest.
Or are you asking if SAS's DI has any tools to allow you to write your own calls to those functions so that you can have Oracle pull out the specific values you want from the JSON text?
@Soulbroda wrote:
What I would like to know is if there is a way I can map this one column to several columns in SAS DI and signify an expression that picks a particular length of the JSONDOC column for each newly created column. Like a SUBSTR or something.
It looks like DI should let you build expressions using database specific functions.
So you should be able to call Oracle functions to parse the JSON. Check with the owner of the data you are querying how they do it.
@Soulbroda wrote:
What I would like to know is if there is a way I can map this one column to several columns in SAS DI and signify an expression that picks a particular length of the JSONDOC column for each newly created column. Like a SUBSTR or something.
Looks like Oracle function JSON_TABLE lets you do what you're after.
Using DIS what I'd be doing:
1. Create a permanent Oracle view with the columns you're after (using JSON_TABLE). Either have an Oracle DBA create such a view for you or create it yourself (implemented in DIS via a user written adhoc job and explicit pass-through SQL). Register the view in SAS Metadata.
2. Use the SAS metadata table object in your regular DIS job.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.