DATA Step, Macro, Functions and more

Reading JSON

Accepted Solution Solved
Reply
Regular Contributor
Posts: 182
Accepted Solution

Reading JSON

Hi,

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

-Bart

Accepted Solutions
Solution
‎12-02-2016 02:33 PM
Community Manager
Posts: 2,763

Re: Reading JSON

[ Edited ]

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;

 

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,743

Re: Reading JSON

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
Regular Contributor
Posts: 182

Re: Reading JSON

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
SAS Super FREQ
Posts: 8,743

Re: Reading JSON

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
Regular Contributor
Posts: 182

Re: Reading JSON

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.

Trusted Advisor
Posts: 1,300

Reading JSON

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.

Regular Contributor
Posts: 182

Re: Reading JSON

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.

Senior User
Posts: 1

Re: Reading JSON

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

Solution
‎12-02-2016 02:33 PM
Community Manager
Posts: 2,763

Re: Reading JSON

[ Edited ]

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;

 

Occasional Contributor
Posts: 7

Re: Reading JSON

when i am running above program getting the below error:

 

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

Community Manager
Posts: 2,763

Re: Reading JSON

@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
Occasional Contributor
Posts: 15

Re: Reading JSON

can we not do end around without reinstalling newer SAS versions.
Community Manager
Posts: 2,763

Re: Reading JSON

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.

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 4592 views
  • 0 likes
  • 7 in conversation