Example of using JSON libname engine

jsonlib_communities.sas

/****************************************/
/* Example of using JSON libname engine */
/* for discovery, then with a JSON map  */
/* Copyright 2016 SAS Institute         */
/* written by Chris Hemedinger          */
/* http://blogs.sas.com/sasdummy        */
/****************************************/

/* split URL for readability */
%let url1=http://communities.sas.com/kntur85557/restapi/vc/categories/id/bi/topics/recent;
%let 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;

/* Let the JSON engine do its thing */
libname posts JSON fileref=topics;
title "Automap of JSON data";

/* examine resulting tables/structure */
proc datasets lib=posts; quit;
proc print data=posts.alldata(obs=20); run;

/* Now create an automatic map to examine */
filename topics temp;
filename jmap "%sysfunc(GETOPTION(WORK))/top.map";

proc http
 url= "&fullurl."
 method="GET"
 out=topics;
run;

libname posts JSON fileref=topics map=jmap automap=create;
title "Using automatic JSON map";
proc datasets lib=posts; quit;

/* Finally, substitute in a custom map  */
filename topics temp;
filename minmap "%sysfunc(GETOPTION(WORK))/minmap.map";

data _null_;
infile datalines;
file minmap;
input;
put _infile_;
datalines;
{
  "DATASETS": [
    {
      "DSNAME": "messages",
      "TABLEPATH": "/root/response/messages/message",
      "VARIABLES": [
        {
          "NAME": "view_href",
          "TYPE": "CHARACTER",
          "PATH": "/root/response/messages/message/view_href",
          "CURRENT_LENGTH": 136
        },
        {
          "NAME": "id",
          "TYPE": "NUMERIC",
          "PATH": "/root/response/messages/message/id"
        },
        {
          "NAME": "subject",
          "TYPE": "CHARACTER",
          "PATH": "/root/response/messages/message/subject",
          "CURRENT_LENGTH": 84
        },
        {
          "NAME": "view_friendly_date",
          "TYPE": "CHARACTER",
          "PATH": "/root/response/messages/message/post_time/view_friendly_date",
          "CURRENT_LENGTH": 12
        },
        {
          "NAME": "datetime",
           "TYPE": "NUMERIC",
		  "INFORMAT": [ "IS8601DT", 19, 0 ],
		  "FORMAT": ["DATETIME", 20],
          "PATH": "/root/response/messages/message/post_time/_",
          "CURRENT_LENGTH": 8
        },
        {
          "NAME": "views",
          "TYPE": "NUMERIC",
          "PATH": "/root/response/messages/message/views/count"
        },
         {
          "NAME": "login",
          "TYPE": "CHARACTER",
          "PATH": "/root/response/messages/message/last_edit_author/login",
          "CURRENT_LENGTH": 15
        },
        {
          "NAME": "likes",
          "TYPE": "NUMERIC",
          "PATH": "/root/response/messages/message/kudos/count"
        }
      ]
    }
  ]
}
;
run;

proc http
 url= "&fullurl."
 method="GET"
 out=topics;
run;

title "Using custom JSON map";
libname posts json fileref=topics map=minmap;
proc datasets lib=posts; quit;

data messages;
 set posts.messages;
run;

proc print data=messages; run;

Is there any tools like SAS XML mapper to create the map file automatically?

Hi @zhanglianbo35, there isn't a GUI tool like XML Mapper. The automap=create option on the LIBNAME JSON statement will create a basic map for you, and then you can edit to adjust for the fields and data types you want.

In your example I see that you are able to map nested JSON attributes. What about arrays? Let's say your root is author instead of messages. Your author object happens to have an array of recent messages. Is it possible to use a map that creates a data set including these items?

The hope is the resulting data set appears the same way one would expect two joined tables would appear.

@mrUlrik A JSON array will appear as its own data set with one column for every member of the array. For example:

filename arrays temp;
data _null_;
file arrays;
put '{
"name":"John",
"age":30,
"cars":["Ford", "BMW", "Fiat"]
}';
run;

libname data json fileref=arrays;

will have a data set named DATA.CARS in the library with the fields cars1, cars2, cars3, and so on. If you want a narrow table instead of wide, you can then use PROC TRANSPOSE (or DATA step with arrays) to reshape the table.