Json output to SAS Dataset from API

sizlingengg — Sun, 07 Jan 2018 11:51:15 GMT

I am using the below code to pull Json data directly into sas dataset from WEB API

%let url1=xyz;
%let url2=abc;
%let fullurl=&url1.&url2;

filename minmap "/xml_data/Biometricmap.map";
filename daily temp;

proc http
url= "&fullurl."
method="Get"
out=daily;
headers 'Content-Type' = 'application/json';
run;

libname posts JSON fileref=daily map=minmap automap=replace;
proc print data=posts.alldata(obs=20); run;

I am not able to fetch the output Its giving me error.

Logs are :

libname posts JSON fileref=daily map=minmap automap=replace;
NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME.
ERROR: Invalid JSON in input near line 1 column 1: Encountered an illegal character.
ERROR: Error in the LIBNAME statement.
proc print data=posts.alldata(obs=20); run;
ERROR: Libref POSTS is not assigned.

this might be because the Json output contains array " [ ", can you please provide how to rectify this

Output Json is :

[
{
"requestdate": "2017-12-14",
"present": "41298",
"averageouttime": " 5:09PM",
"averageintime": "10:52AM",
"registeredusers": "156495"
}
]

This is what i want in dataset as

requestdate present averageouttime averageintime registeredusers

2017-12-14

41298

5:09PM

10:52AM

156495

So please help me with the correct code ??

Re: Json output to SAS Dataset from API

ChrisHemedinger — Sun, 07 Jan 2018 15:41:54 GMT

What's the map/automap doing for you? Are you wanting to control how the data values are read and convert the dates/times into proper SAS dates and times?

This code reads the data as is -- all values are read in as CHAR.

filename daily temp;
data _null_;
 file daily;
 infile datalines;
 input;
 put _infile_;
datalines;
[
{
"requestdate": "2017-12-14",
"present": "41298",
"averageouttime": " 5:09PM",
"averageintime": "10:52AM",
"registeredusers": "156495"
}
]
;
run;

libname posts JSON fileref=daily ;
proc datasets lib=posts;
run;

data result;
 set posts.root;
run;

proc print data=result; run;

If you want to convert some of the variables to proper SAS values you can do that in DATA step:

data result;
 set posts.root (rename=( requestdate=_in_requestdate ));
 /* fix the date value */
 length requestdate 8;
 format requestdate YYMMDD10.;
 requestdate = input (_in_requestdate, YYMMDD10.);
 drop _in_:;
run;

Or you can take the generated JSON map and modify it by specifying the appropriate INFORMATs and FORMATs, as I described in this blog post.

topic Re: Json output to SAS Dataset from API in SAS Programming

Json output to SAS Dataset from API

Re: Json output to SAS Dataset from API