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
PROC Star

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.

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
Haris
Lapis Lazuli | Level 10

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

Quentin
PROC Star

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
    ]",
Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 161 views
  • 1 like
  • 2 in conversation