Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Example of using JSON libname engine
/****************************************/
/* 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;
@zhanglianbo35

This comment has been minimized.

Copy link

@zhanglianbo35 zhanglianbo35 commented Feb 4, 2020

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

@cjdinger

This comment has been minimized.

Copy link
Owner Author

@cjdinger cjdinger commented Feb 4, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment