BookmarkSubscribeRSS Feed
sizlingengg
Calcite | Level 5

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

1 REPLY 1
ChrisHemedinger
Community Manager

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.

Check out SAS Innovate on-demand content! Watch the main stage sessions, keynotes, and over 20 technical breakout sessions!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1298 views
  • 0 likes
  • 2 in conversation