BookmarkSubscribeRSS Feed
Soulbroda
Obsidian | Level 7

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.

 

Delimited File.PNG

9 REPLIES 9
ballardw
Super User

You may want to check the LIBNAME statement, JSON engine documentation as it appears that you  may have a JSON file.

Soulbroda
Obsidian | Level 7

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. 

Reeza
Super User
Your data appears pretty clearly formatted. Do you expect it to stay this way or do you expect deviations?

If you don't expect significant deviations you can use a data step with SCAN() to pull the elements.

Another option is to write it out as JSON and read it back in using LIBNAME but that could get annoying for sure.

I don't know that SAS has a process for ingesting JSON via a DB at the moment. But if any application has that feature, it would be DI.
Tom
Super User Tom
Super User

@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
Obsidian | Level 7
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.
Tom
Super User Tom
Super User

@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.

https://documentation.sas.com/?docsetId=etlug&docsetTarget=p1jpm6cdhxnmk5n1klbkjs02pbep.htm&docsetVe...

 

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. 

Patrick
Opal | Level 21

@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.

Bro
Calcite | Level 5 Bro
Calcite | Level 5
Hi
I have the same issue can you please help me with that

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

SAS Enterprise Guide vs. SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 3698 views
  • 9 likes
  • 6 in conversation