JSON is a key interchange format between applications and API-based services. SAS has built-in tools to convert JSON to SAS data for analytics, as well as to convert data from SAS to JSON output to integrate with other applications.
Watch the webinar
View this on-demand webinar to learn how to use SAS to read JSON data sources and create JSON output within SAS programming.
You will learn:
- What is JSON and how is it used?
- How to use the JSON libname engine to read JSON into SAS data sets.
- How to use JSON maps to control how SAS reads and interprets the raw JSON data.
- How to use PROC JSON to create JSON output in SAS.
Learn more:
Q&A
Is JSON supported in standard SAS EG (any certain version) or is it an add-on needing special versions /cost? Thanks.
JSON engine is part of Base SAS, unlike some database engines that are SAS/Access products that you have to add on separately. It's like the XML engine, which is also built into Base SAS. You don't need anything additional. Everything I showed was using EG, but it would work in SAS Studio or in just PC SAS or whatever SAS you're using. It all works exactly the same, doesn't require a different a special version of EG or of SAS Studio. The JSON engine itself was added in an earlier release of SAS 9.4. It's not new, but if you're using SAS 9.3 or SAS 9.2, which amazingly some people still do, it won't be in those distributions.
Can JSON be used in Base SAS or SAS Studio, or can it only be used in the SAS Viya environment?
It can be used in any modern version of SAS. Fun fact, it was actually added in SAS Viya first. It was then backported into one the SAS 9.4 maintenance releases. It's a good example of how some innovation that was needed in SAS Viya actually benefited all of us users in SAS 9.
Sometimes there is JSON embedded within another JSON. How are those handled?
This gets a bit like Inception where you've got JSON with name-value pairs and then one of those values is actually this whole other JSON segment. So, the JSON engine does not recurse and interpret the value as JSON data. Instead, the value you would get would be just the text representation of that JSON. If you want the JSON engine to read it, you will have to do an additional step of detecting that case, write it out to another file, and then point the JSON engine to that file. You could also do more like what I showed earlier with the newline delimited JSON, where you could pull all that JSON out and parse together. Let's say you've got 10 records and each one of them has a value with a JSON segment. Just assemble all those things together into one file, put a JSON wrapper around it, and then point the LIBNAME JSON engine to that to read it all at once. There are things you can do to manipulate it. It's tricky when people are using JSON to store more JSON.
If a JSON field has value null for all records, is there a way to tell the JSON engine to read it into a character variable vs numeric variable? I don't think it supports dbsastype. Does it?
DBSASTYPE is the database engine option that a lot of our access engines support, but I do not think the JSON engine supports that. Instead, we have the JSON map that lets you tell SAS how to interpret different values. I'm not sure if the JSON map lets you decide to do something different with the null you did see in the data table that I showed. Text representation of null is there, so you could do something there to keep the null value spelled out as null. Otherwise, as you saw, if you just let the engine read it directly, it's going to be interpreted as missing.
Do you have examples or references for accessing secure JSON? (Like off s3, for example)
I don't have examples of that but think of it as if you're accessing JSON from a secure location. S3, we have a SAS procedure PROC S3 that allows you to. S3 is a cloud storage format or Amazon I guess. We have Proc S3 that allows you to go and get whatever resource you need from there and then you pull that into your SAS session, assign a file ref to it, and then you could point the libname JSON engine at it and read it just the way I showed here. The difference would be instead of where you saw me using PROC HTTP to pull information from a web service, you'd use Proc S3 to pull information from your S3 storage location into your SAS session and read it the same way.
In PROC JSON, when a value is missing is there a way to tell the procedure not to print the key/value or value instead of a null value?
In JSON, missing means null. It's going to be represented as null using the keyword null in the JSON syntax and then SAS will interpret that as missing. By default, it'll be represented as a dot. The earlier question was about retaining that null text just to indicate it right in my output that that was null. Again, you might have to go to the ALLDATA table to make that happen. Otherwise, it is just going to be by default interpreted as NULL if that was what you wanted. A lot of times, when we have data that's null or that's missing, it's up to you and your understanding of the domain and the downstream use of it what you want to do with that missing value. Do you want to discard it? Do you want to turn it into a 0? What is the best representation for what it is you need to do. That's all got to be kind of that business logic, or it has to be decided in your data step, Proc SQL, or whatever it is you're using.
We have tables with a single variable containing JSON data, what's best practice for working with the single variable with JSON content?
You see that a lot with responses from Proc SQL output where you've got a single name value pair. The name is like response or something like that and then the value is just this big, long JSON. The thing to do there is alluding to the answer I gave before, but is capture that JSON segment, that value segment and write it out to another file. You would use data step in that case to take that value. Use the file statement to write it out to another external file, and then in turn use the JSON engine again to point to that file that you've written out and interpret that as the data that you need. It's at this level of interaction to get down to the actual data that you want to interpret. It's what happens when you're getting JSON from some resource that was designed to populate something like a UI in a web form. But in your case, we're wanting to just read it as data, and it wasn't designed for that. We can still do it, it just takes a little bit more manipulation to make it work.
Will the engine also give errors in the JSON file in order to be able to correct it?
No, the JSON engine just tells you when the content is "not valid JSON" -- you have to examine the actual content to find the error. There are third-party tools that might help. I often use an online "JSON Pretty Print" site to provide validation and clues.
Will you show JSON in a database structure?
I should some of these in my demos. See this community article for more details.
Click image to register for webinar