BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bheinsius
Lapis Lazuli | Level 10
Hi,

Is there an easy way to read JSON (JavaScript Object Notation) formatted data in SAS?

-Bart
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

I'm so happy to be able to update this post with the answer: a JSON libname engine is now in Base SAS 9.4 (maint 4).

 

Example:

 

filename resp temp;
 
/* Neat service from Open Notify project */
proc http 
 url="http://api.open-notify.org/astros.json"
 method= "GET"
 out=resp;
run;
 
/* Assign a JSON library to the HTTP response */
libname space JSON fileref=resp;
 
/* Print result, dropping automatic ordinal metadata */
title "Who is in space right now? (as of &sysdate)";
proc print data=space.people (drop=ordinal:);
run;

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

12 REPLIES 12
Cynthia_sas
SAS Super FREQ
Hi:
That would be an easier question to answer if I knew what a JSON data file looked like. SAS can read data in a lot of different formats -- how easy it is depends on the format of the input data. Your JSON data could be in XML format and able to be read with the SAS XML LIBNAME engine or it could be "free-format" data in which case, you'd need a custom INPUT statement.

Can you post a sample of the data here????

And, when you are posting data, if it contains special characters, such as < and > please read this previous forum posting about how to maintain line indenting and special characters:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

Basically, you use the [pre] and [/pre] tags around your code and data samples in order to maintain indenting and spacing.

cynthia
bheinsius
Lapis Lazuli | Level 10
Hi Cynthia,

Thanks for your answer.

From wikipedia:
JSON (an acronym for JavaScript Object Notation (pronounced /dʒeɪsɔːn/)) is a lightweight text-based open standard designed for human-readable data interchange. It is derived from the JavaScript programming language for representing simple data structures and associative arrays, called objects. Despite its relationship to JavaScript, it is language-independent, with parsers available for virtually every programming language.
The JSON format is often used for serializing and transmitting structured data over a network connection. It is primarily used to transmit data between a server and web application, serving as an alternative to XML.


An example is the following:
[pre]
{
"firstName": "John",
"lastName": "Smith",
"age": 25,
"address":
{
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021"
},
"phoneNumber":
[
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "fax",
"number": "646 555-4567"
}
]
}
[/pre]
Do I have to write my own parser?

-Bart
Cynthia_sas
SAS Super FREQ
Hi:
Without knowing a LOT more about JSON, I do have some comments. It says in the Wikipedia definition that some folks use JSON format as an alternative to XML...can you get this data in XML format??? If so, you might be able to use the SAS XML Libname engine to read it.

If not, then is there a chance you could write the dat to an HTML file?? If so, then you might be able to read the resulting HTML table with the HTTP engine for the FILENAME statement.

It says that the JSON format is used for transmitting data between a server and a web application. What web application do you currently have receiving the JSON data??

I see a few issues with the data that you posted. For example -- what is the significance of the indenting??? And, what happens or how does the data look if you have more that one observation?? I can see that this particular description defines 1 "record" or 1 "observation", but what happens when you have multiple records -- what defines the end of 1 observation and the beginning of the next observation??

Also, what about the address: item and the phoneNumber: item??? The indenting underneath each items eems to indicate that the items underneath address: and underneath phoneNumber: are grouped, somehow??? Additionally, there seems to be some significance to the use of the curly brace { versus the square bracket [ -- somehow it seems like the square bracket begins a "set" of paired variables or an array definition -- I would have expected to see type1, number1, type2 and number2 -- instead of reusing type and number as variable names over again.

It's really too bad that your variable/value pairs aren't in a form something like this:
[pre]
firstName=John
lastName=Smith
age=25
. . . more . . .
phoneNumber_type1=home
phoneNumber_number1=212 555-1234
phoneNumber_type2=fax
phoneNumber_number2=646 555-4567
[/pre]

If they were just a bit different, then you might be able to use named INPUT, as described here:
http://support.sas.com/documentation/cdl/en/lrdict/63026/HTML/default/viewer.htm#a000148147.htm

You'd still need to use the variable names in the INPUT statement, so you could control the variable type, label, etc, but named INPUT would be easier.

If you parse the items yourself, I would expect that you'd read a line of input as a TEXT string and then parse out the variable and the value using SAS functions.

Can I ask why the data is in JSON format and not stored in whatever format the web application uses???

cynthia
bheinsius
Lapis Lazuli | Level 10

I never saw this reply until now (email SAS Communities email notifications don't work for me).

JSON *is* the format that the web application uses; it sends serialized data to my SAS Stored Process in JSON format, I have to parse it in SAS.

I guess there is no easy way, thanks.

FriedEgg
SAS Employee

There is no build in functionality to interpert a JSON data structure in SAS.  The recommended option is to initiate an outside utility to conver the JSON object into a XML document.  There are lots of options to do this.  There are .NET applications, perl scripts, etc...  Have the SAS Stored Process execute the necssary commands and export a more easily usable XML file.  I know there are published documents around the web showing this type of process.  I think maybe an entry in one of the SAS official Blogs.  Take a look there for additional information.

bheinsius
Lapis Lazuli | Level 10

Thanks for the suggestion.

XML isn't a handy option either, the webapp gives lots of different responses, I would have to create xmlmaps for each.

I was looking for something simpler, like a proc or macro that creates a SAS data set from a JSON string.

ravinsun_gmail_com
Calcite | Level 5

Is there an easy way to read JSON (JavaScript Object Notation) formatted data in SAS? Do we have any answer for this ?

ChrisHemedinger
Community Manager

I'm so happy to be able to update this post with the answer: a JSON libname engine is now in Base SAS 9.4 (maint 4).

 

Example:

 

filename resp temp;
 
/* Neat service from Open Notify project */
proc http 
 url="http://api.open-notify.org/astros.json"
 method= "GET"
 out=resp;
run;
 
/* Assign a JSON library to the HTTP response */
libname space JSON fileref=resp;
 
/* Print result, dropping automatic ordinal metadata */
title "Who is in space right now? (as of &sysdate)";
proc print data=space.people (drop=ordinal:);
run;

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
arpitagarwal512
Calcite | Level 5

when i am running above program getting the below error:

 

ERROR: The JSON engine cannot be found.
ERROR: Error in the LIBNAME statement.

ChrisHemedinger
Community Manager

@arpitagarwal512 - the JSON engine was added in SAS 9.4 Maint 4.  You can run:

 

proc product_status; run;

To see your version.  It should return something like:

 

For Base SAS Software ...
   Custom version information: 9.4_M4
   Image version information: 9.04.01M4P110916
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
rboire
Calcite | Level 5
can we not do end around without reinstalling newer SAS versions.
ChrisHemedinger
Community Manager

In earlier versions of SAS, you would need to simply use DATA step or PROC DS2 to parse the JSON.  It's not nearly as elegant as the JSON libname engine, but for simple JSON it works.

 

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 12 replies
  • 11094 views
  • 1 like
  • 7 in conversation