DATA Step, Macro, Functions and more

Json output to SAS Dataset from API

Reply
User
Posts: 1

Json output to SAS Dataset from API

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 ?? 

Community Manager
Posts: 3,462

Re: Json output to SAS Dataset from API

Posted in reply to sizlingengg

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.

Ask a Question
Discussion stats
  • 1 reply
  • 234 views
  • 0 likes
  • 2 in conversation