topic Re: Reading JSON in SAS Programming

Reading JSON

bheinsius — Mon, 08 Nov 2010 12:33:55 GMT

Hi,

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

-Bart

Re: Reading JSON

Cynthia_sas — Mon, 08 Nov 2010 15:44:14 GMT

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

Re: Reading JSON

bheinsius — Mon, 08 Nov 2010 16:01:50 GMT

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

Re: Reading JSON

Cynthia_sas — Tue, 09 Nov 2010 00:08:49 GMT

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

Re: Reading JSON

bheinsius — Fri, 27 Jan 2012 15:06:30 GMT

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.

Reading JSON

FriedEgg — Fri, 27 Jan 2012 21:04:22 GMT

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.

Re: Reading JSON

bheinsius — Sat, 28 Jan 2012 20:18:50 GMT

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.

Re: Reading JSON

ravinsun_gmail_com — Thu, 15 Oct 2015 16:08:01 GMT

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

Re: Reading JSON

ChrisHemedinger — Fri, 02 Dec 2016 19:33:03 GMT

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;

Re: Reading JSON

arpitagarwal512 — Thu, 25 May 2017 12:25:23 GMT

when i am running above program getting the below error:

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

Re: Reading JSON

ChrisHemedinger — Thu, 25 May 2017 19:42:01 GMT

@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

Re: Reading JSON

rboire — Thu, 25 May 2017 21:42:05 GMT

can we not do end around without reinstalling newer SAS versions.

Re: Reading JSON

ChrisHemedinger — Fri, 26 May 2017 11:35:10 GMT

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.