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.

BASUG is hosting free webinars ! Next up: Art Carpenter on February 28 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
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
    ]",
BASUG is hosting free webinars ! Next up: Art Carpenter on February 28 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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