How To Read Multiple Files In Sas Using Filename

JSON is the new XML. The number of SAS users who demand to access JSON information has skyrocketed, thanks mainly to the proliferation of REST-based APIs and spider web services. Because JSON is structured data in text format, we've been able to offer simple parsing techniques that utilize Information pace and most recently PROC DS2. But finally*, with SAS 9.iv Maintenance 4, we have a born LIBNAME engine for JSON.

Simple JSON case: Who is in infinite correct now?

Speaking of skyrocketing, I discovered a cool spider web service that reports who is in space right now (at to the lowest degree on the International Infinite Station). It's actually a perfect example of a REST API, because it does only that one thing and it's easily integrated into whatever process, including SAS. It returns a simple stream of data that can be hands mapped into a tabular structure. Here's my instance code and results, which I produced with SAS nine.4 Maintenance 4.

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

JSON who is in space
But what if your JSON information isn't so elementary? JSON can represent information in nested structures that tin exist many layers deep. These cases crave some additional mapping to transform the JSON representation to a rectangular data table that nosotros tin apply for reporting and analytics.

JSON map instance: About recent topics from SAS Back up Communities

In a previous mail I shared a PROC DS2 program that uses the DS2 JSON bundle to call and parse our SAS Support Communities API. The parsing process is robust, but it requires quite a bit of fore knowledge nearly the construction and fields within the JSON payload. It also requires many lines of code to excerpt each field that I desire.

Here'south a revised laissez passer that uses the JSON engine:

            /* split URL for readability */            %allow            url1=http://communities.sas.com/kntur85557/restapi/vc/categories/id/bi/topics/recent;            %allow            url2=?restapi.response_format=json%str(&)restapi.response_style=-types,-null,view;            %let            url3=%str            (&)page_size=100;            %let            fullurl=&url1.&url2.&url3;            filename            topics temp;            proc http            url=            "&fullurl."            method="GET"            out=topics;            run;            /* Permit the JSON engine do its affair */            libname            posts JSON            fileref=topics;            title            "Automap of JSON data";            /* examine resulting tables/structure */            proc datasets            lib=posts;            quit;            proc print            information=posts.alldata(obs=20            );            run;

Thanks to the many layers of information in the JSON response, here are the tables that SAS creates automatically.

json Auto tables
At that place are 12 tables that contain diverse components of the message data that I want, plus the ALLDATA member that contains everything in i linear table. ALLDATA is skilful for examining construction, simply not for analysis. You can come across that it's basically name-value pairs with no data types/formats assigned.

json ALLDATA
I could use DATA steps or PROC SQL to merge the various tables into a single denormalized table for my reporting purposes, simply in that location is a better way: define and apply a JSON map for the libname engine to utilize.

To get started, I demand to rerun my JSON libname assignment with the AUTOMAP option. This creates an external file with the JSON-formatted mapping that SAS generates automatically. In my example here, the file lands in the WORK directory with the name "top.map".

            filename            jmap            "%sysfunc(GETOPTION(Piece of work))/elevation.map";            proc http            url=            "&fullurl."            method="Get"            out=topics;            run;            libname            posts JSON            fileref=topics map=jmap automap=create;

This generated map is quite long -- over 400 lines of JSON metadata. Here's a snippet of the file that describes a few fields in just ane of the generated tables.

"DSNAME": "messages_message", "TABLEPATH": "/root/response/messages/message", "VARIABLES": [ {   "NAME": "ordinal_messages",   "Type": "ORDINAL",   "PATH": "/root/response/messages" }, {   "Proper noun": "ordinal_message",   "TYPE": "ORDINAL",   "PATH": "/root/response/messages/message" }, {   "NAME": "href",   "TYPE": "Character",   "PATH": "/root/response/messages/bulletin/href",   "CURRENT_LENGTH": 19 }, {   "NAME": "view_href",   "Blazon": "CHARACTER",   "PATH": "/root/response/letters/message/view_href",   "CURRENT_LENGTH": 134 },

By using this map as a starting signal, I can create a new map file -- one that is simpler, much smaller, and defines just the fields that I want. I can reference each field by its "path" in the JSON nested structure, and I tin besides specify the types and formats that I want in the final data.

In my new map, I eliminated many of the tables and fields and ended upwardly with a file that was but about sixty lines long. I also applied sensible variable names, and I even specified SAS formats and informats to transform some columns during the import process. For case, instead of reading the message "datetime" field every bit a character string, I coerced the value into a numeric variable with a DATETIME format:

{   "Proper noun": "datetime",    "Type": "NUMERIC",   "INFORMAT": [ "IS8601DT", 19, 0 ],   "FORMAT": ["DATETIME", 20],   "PATH": "/root/response/messages/message/post_time/_",   "CURRENT_LENGTH": 8 },

I called my new map file 'minmap.map' so re-issued the libname without the AUTOMAP pick:

            filename            minmap            'c:\temp\minmap.map';            proc http            url=            "&fullurl."            method="GET"            out=topics;            run;            libname            posts json            fileref=topics map=minmap;            proc datasets            lib=posts;            quit;            data            messages;            set            posts.messages;            run;

Here'south a snapshot of the single data ready as a result.

JSON final data
I think you'll hold that this event is much more usable than what my kickoff laissez passer produced. And the amount of code is much smaller and easier to maintain than whatsoever previous SAS-based process for reading JSON.

Here'south the consummate program in a public GitHub gist, including my custom JSON map.

* By the way, the JSON libname engine actually made its debut every bit role of SAS Visual Data Mining and Machine Learning, part of SAS Viya. This is a good example of how work on SAS Viya continues to do good the users of the SAS 9.4 architecture.