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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.