topic Re: Read JSON without flattening ARRAYS in SAS Programming

Read JSON without flattening ARRAYS

Haris — Thu, 30 Mar 2023 18:49:24 GMT

I am trying to read a JSON file with 30 arrays. Here's the code:

filename in "Source.json";
filename map 'WorkingMap.Map';
libname in json map=Map automap=reuse;

libname j json "Source.json" map=Map;

By default this syntax creates a separate table for each array. For the example below, the code above creates Proc1-Proc4 columns in a table PROC with Ordinal_Root link to the main ROOT dataset.

"proc": [
      2800,
      2980,
      4090,
      4170
    ],

I have to merge multiple files into one for my purposes and also aggregate multiple columns into one. On top of that, I am processing dozens of JSONS that need to be stacked to there's a ton of re-merging going on.

Is there an option or a mapping method/trick to read arrays as simple text strings? For the PROC example above, rather than a dedicated separate table with multiple columns for each value of the array, I would like a single column that contains them all in one cell just like in the JSON:

[ 2800, 2980, 4090, 4170 ]

Need help!!!

Re: Read JSON without flattening ARRAYS

Quentin — Thu, 30 Mar 2023 19:25:52 GMT

I'm confused as to what you want as the SAS data from reading that JSON.

If you read the data:

"proc": [
      2800,
      2980,
      4090,
      4170
    ],

Do you want the output to be a SAS dataset with one record and one character variable named PROC with the value "[ 2800, 2980, 4090, 4170 ]" ?

Or do you want the output to be a SAS dataset with one numeric variable PROC with four records, i.e.:

Or do you want something else?

Be sure to check out the alldata table, that might be helpful to you.

Re: Read JSON without flattening ARRAYS

Haris — Thu, 30 Mar 2023 19:28:24 GMT

I want a single column PROC in the SAS dataset that contains value PROC = "[ 2800, 2980, 4090, 4170 ]".

Re: Read JSON without flattening ARRAYS

Quentin — Thu, 30 Mar 2023 20:03:15 GMT

I don't think you can do that with the JSON engine. You basically want to read an array of JSON elements into a single character variable.

If you write your own program to parse the json file using INPUT, you should be able to do it.

Or maybe you could pre-process the JSON file to convert the arrays into string values, i.e. change:

"proc": [
      2800,
      2980,
      4090,
      4170
    ],

to:

"proc": "[
      2800,
      2980,
      4090,
      4170
    ]",