Splitting string variable into multiple columns in SAS DI Studio

[Soulbroda]

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.

[ballardw]

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

[Soulbroda]

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]

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]

---

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

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.

[Reeza]

SCAN() function.

[Tom]

---

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

---

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

Hi
I have the same issue can you please help me with that