BookmarkSubscribeRSS Feed
Haris
Lapis Lazuli | Level 10

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!!! 

3 REPLIES 3
Quentin
Super User

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

PROC
2800
2980
4090
4170

Or do you want something else?

 

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

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
Haris
Lapis Lazuli | Level 10

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

Quentin
Super User

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
    ]",
The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 812 views
  • 1 like
  • 2 in conversation