SAS 9.4 introduced PROC JSON, which allows you to create JSON output from a data set. But it wasn't until SAS 9.4 Maint 3 that we have a built-in method to parse JSON content. That method was added as a DS2 package: the JSON package.
Here's an example of the method working -- using an API that powers this very community! (That's "meta" for you.) It's my first real DS2 program, so I'm open to feedback. I already know of a couple of improvements I should make, but I want to share it now as I think it's good enough to help others who are looking to do something similar.
The program requires SAS 9.4 Maint 3. It also works fine in the most recent version of SAS University Edition (using SAS Studio 3.4). All of the code runs using just Base SAS procedures.
/* DS2 program that uses a REST-based API */
/* Uses http package for API calls */
/* and the JSON package (new in 9.4m3) */
/* to parse the result. */
proc ds2;
data messages (overwrite=yes);
/* Global package references */
dcl package json j();
/* Keeping these variables for output */
dcl double post_date having format datetime20.;
dcl int views;
dcl nvarchar(128) subject author board;
/* these are temp variables */
dcl varchar(65534) character set utf8 response;
dcl int rc;
drop response rc;
method parseMessages();
dcl int tokenType parseFlags;
dcl nvarchar(128) token;
rc=0;
* iterate over all message entries;
do while (rc=0);
j.getNextToken( rc, token, tokenType, parseFlags);
* subject line;
if (token eq 'subject') then
do;
j.getNextToken( rc, token, tokenType, parseFlags);
subject=token;
end;
* board URL, nested in an href label;
if (token eq 'board') then
do;
do while (token ne 'href');
j.getNextToken( rc, token, tokenType, parseFlags );
end;
j.getNextToken( rc, token, tokenType, parseFlags );
board=token;
end;
* number of views (int), nested in a count label ;
if (token eq 'views') then
do;
do while (token ne 'count');
j.getNextToken( rc, token, tokenType, parseFlags );
end;
j.getNextToken( rc, token, tokenType, parseFlags );
views=inputn(token,'5.');
end;
* date-time of message (input/convert to SAS date) ;
* format from API: 2015-09-28T10:16:01+00:00 ;
if (token eq 'post_time') then
do;
j.getNextToken( rc, token, tokenType, parseFlags );
post_date=inputn(token,'anydtdtm26.');
end;
* user name of author, nested in a login label;
if (token eq 'author') then
do;
do while (token ne 'login');
j.getNextToken( rc, token, tokenType, parseFlags );
end;
* get the author login (username) value;
j.getNextToken( rc, token, tokenType, parseFlags );
author=token;
output;
end;
end;
return;
end;
method init();
dcl package http webQuery();
dcl int rc tokenType parseFlags;
dcl nvarchar(128) token;
dcl integer i rc;
/* create a GET call to the API */
/* 'sas_programming' covers all SAS programming topics from communities */
webQuery.createGetMethod(
'http://communities.sas.com/kntur85557/' ||
'restapi/vc/categories/id/sas_programming/posts/recent' ||
'?restapi.response_format=json' ||
'&restapi.response_style=-types,-null&page_size=100');
/* execute the GET */
webQuery.executeMethod();
/* retrieve the response body as a string */
webQuery.getResponseBodyAsString(response, rc);
rc = j.createParser( response );
do while (rc = 0);
j.getNextToken( rc, token, tokenType, parseFlags);
if (token = 'message') then
parseMessages();
end;
end;
method term();
rc = j.destroyParser();
end;
enddata;
run;
quit;
/* Add some basic reporting */
proc freq data=messages noprint;
format post_date datetime11.;
table post_date / out=message_times;
run;
ods graphics / width=2000 height=600;
title '100 recent message contributions in SAS Programming';
title2 'Time in GMT';
proc sgplot data=message_times;
series x=post_date y=count;
xaxis minor label='Messages';
yaxis label='Time created' grid;
run;
title 'Board frequency for recent 100 messages';
proc freq data=messages order=freq;
table board;
run;
title 'Detailed listing of messages';
proc print data=messages;
run;
title;Here is some partial output:
Looking forward to your comments...
Chris
The DS2 language does not include syntax for consuming text files. Downloading the JSON to a text file first means that you will have to load it into a table using other means before processing it with DS2. If you are having trouble with the size of the JSON, try making the response variable larger size. For example change this:
dcl varchar(65534) character set utf8 response;
to this:
dcl varchar(1000000) character set utf8 response;
You won't be able to store all 1000000 charactes in your VARCHAR variable to a table, but you can manipulate it with DS2, so you can parse the JSON text using the JSON package as shown in the example.
As for your second question, there really isn't a standard JSON structure - it's completely flexible. It is often useful to be able to prototype JSON data extraction routines to see how the tokens break out. Your could use code something like this:
/* Put some JSON in a macro variable for use in testing */
%let json= '{
"provider_urls": [
"https://www.modahealth.com/cms-data/providers-AK.json",
"https://www.modahealth.com/cms-data/providers-OR.json"
],
"formulary_urls": [
"https://www.modahealth.com/cms-data/drugs-AK.json",
"https://www.modahealth.com/cms-data/drugs-OR.json"
],
"plan_urls": [
"https://www.modahealth.com/cms-data/plans-AK.json",
"https://www.modahealth.com/cms-data/plans-OR.json"
]
}';
proc ds2;
data tokens/overwrite=yes;
dcl package json j();
dcl nchar(1024) token;
dcl int tokenType;
dcl char(1) string number bool_true bool_false delimiter;
method init();
dcl varchar(1000000) json;
dcl nchar(1024) thisVar;
dcl int rc parseFlags;
/* JSON for prototyping */
json=%superq(json);
rc = j.createParser();
if (rc) then do;
put 'Error' rc= ': Could not create JSON parser.';
stop;
end;
rc = j.setParserInput(json);
if (rc) then do;
put 'Error' rc= ': setParserInput failed.';
stop;
end;
/* Use the parser to parse the JSON */
/* RC of 0 means all went well. Otherwise, an error occurred */
do until (rc ne 0);
j.getNextToken( rc, token, tokenType, parseFlags );
string=if j.isstring(tokenType) then 'Y' else 'N';
number=if j.isnumeric(tokenType) then 'Y' else 'N';
bool_true=if j.isbooleantrue(tokenType) then 'Y' else 'N';
bool_false=if j.isbooleanfalse(tokenType) then 'Y' else 'N';
delimiter=if tokenType in (16,32,64,128) then 'Y' else 'N';
output;
end;
end;
enddata;
run;
quit;
proc print data=tokens; run;
and then look at how the JSON tokens parsed out:
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
-
21166 views
-
14 likes
-
-
