topic Re: Splitting string variable into multiple columns in SAS DI Studio in SAS Studio

Splitting string variable into multiple columns in SAS DI Studio

Soulbroda — Mon, 10 Feb 2020 17:23:35 GMT

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.

Re: Splitting string variable into multiple columns in SAS DI Studio

ballardw — Mon, 10 Feb 2020 17:28:02 GMT

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

Re: Splitting string variable into multiple columns in SAS DI Studio

Soulbroda — Mon, 10 Feb 2020 18:17:10 GMT

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.

Re: Splitting string variable into multiple columns in SAS DI Studio

Reeza — Mon, 10 Feb 2020 18:20:57 GMT

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.

Re: Splitting string variable into multiple columns in SAS DI Studio

Tom — Mon, 10 Feb 2020 18:24:31 GMT

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

Re: Splitting string variable into multiple columns in SAS DI Studio

Soulbroda — Mon, 10 Feb 2020 21:07:48 GMT

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.

Re: Splitting string variable into multiple columns in SAS DI Studio

Reeza — Mon, 10 Feb 2020 21:17:25 GMT

SCAN() function.

Re: Splitting string variable into multiple columns in SAS DI Studio

Tom — Mon, 10 Feb 2020 22:01:32 GMT

@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&docsetVersion=4.904&locale=en#p0j095zccgtk6in1xjfgso9zzx6w

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.

Re: Splitting string variable into multiple columns in SAS DI Studio

Patrick — Tue, 11 Feb 2020 01:03:07 GMT

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

Re: Splitting string variable into multiple columns in SAS DI Studio

Bro — Wed, 15 Mar 2023 12:19:04 GMT

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