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 ??
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.