I want to use Proc http to download a json file from a website, then read the data and manipulate it.
I have never used Proc http before and have two questions.
First Question - I am getting symbolic reference warnings and don't know if these are warning I can ignore or not? My URL address is very long and I get the following warnings:
WARNING: Apparent symbolic reference SYSPARM_DISPLAY_VALUE not resolved.
WARNING: Apparent symbolic reference SYSPARM_EXCLUDE_REFERENCE_LINK not resolved.
WARNING: Apparent symbolic reference SYSPARM_FIELDS not resolved.
WARNING: Apparent symbolic reference SYSPARM_LIMIT not resolved.
The code I am running is:
filename out TEMP;
filename cacm TEMP;
proc http
url="https://abc.service-now.com/api/now/table/u_cacm_pl_uw_mngr_type_8?sysparm_query=cref_referred_dateO..."
method="GET"
out=out
headerout=cacm
AUTH_NEGOTIATE
WEBUSERNAME="admin"
WEBPASSWORD="xxxxxxxxxx";
run;
libname CACM xlsx "%sysfunc(pathname(out))" ;
proc copy inlib=cacm outlib=WORK;
run;
My second question - assuming that I don't need to worry about the warnings or if I can resolve them and mt PROC HTTP successfully runs (it seems to run with no errors), then how do I read the dataset? In this case, I would like to make JSON output as a SAS dataset and mainpulate it. My code runs with no errors but also no output.
The test JSON output should look loke this:
Test REST Response:
{"result":[
{"c_number":"3364","cp_policy.contract_state_code":"OH","cref_referred_date":"2019-09-23","cref_response_date":"2019-09-27","c_case_open_timestamp":"2019-09-23"},
{"c_number":"2489","cp_policy.contract_state_code":"CO","cref_referred_date":"2019-07-30","cref_response_date":"2019-07-31","c_case_open_timestamp":"2019-07-30"},
{"c_number":"3571","cp_policy.contract_state_code":"WA","cref_referred_date":"2019-10-09","cref_response_date":"2019-10-18","c_case_open_timestamp":"2019-10-08"},
{"c_number":"2752","cp_policy.contract_state_code":"IA","cref_referred_date":"2019-08-15","cref_response_date":"2019-08-27","c_case_open_timestamp":"2019-08-15"},
{"c_number":"2752","cp_policy.contract_state_code":"IA","cref_referred_date":"2019-08-15","cref_response_date":"2019-08-27","c_case_open_timestamp":"2019-08-15"},
{"c_number":"3016","cp_policy.contract_state_code":"NE","cref_referred_date":"2019-08-29","cref_response_date":"2019-09-09","c_case_open_timestamp":"2019-08-29"},
{"c_number":"3336","cp_policy.contract_state_code":"MO","cref_referred_date":"2019-09-23","cref_response_date":"2019-09-23","c_case_open_timestamp":"2019-09-23"},
{"c_number":"3336","cp_policy.contract_state_code":"MO","cref_referred_date":"2019-09-23","cref_response_date":"2019-09-23","c_case_open_timestamp":"2019-09-23"},
{"c_number":"3336","cp_policy.contract_state_code":"MO","cref_referred_date":"2019-09-23","cref_response_date":"2019-09-23","c_case_open_timestamp":"2019-09-23"},
{"c_number":"2817","cp_policy.contract_state_code":"WI","cref_referred_date":"2019-08-19","cref_response_date":"2019-08-27","c_case_open_timestamp":"2019-08-19"}
]}
I appreciate any help or guidance!
My code was wrong - should be:
data _null_;
rc = jsonpp("out","log");
run;
But the ALLDATA member has the messages. Your API call was not authenticated properly. You're trying to use Basic authentication (user/pw). Maybe the user/pw is wrong, or maybe the service isn't configured to allow Basic auth. It's often disallowed these days as OAuth2 is more standard and secure.
At the least, your PROC HTTP statement should use AUTH_BASIC instead of NEGOTIATE, as that's the protocol you want.
Try your API call in another interactive environment to get it working before trying to script it with SAS. Postman is a good one. See tips here.
Thanks Reeza for your suggestions. I have re-read Chris' blog and that helped me out. Still trying to get data to come through and am very close, but not quite there.
I am using 9.4_M5 and am trying to use the JSON engine.
Regarding the warnings, they are informational but don't hurt anything. However, I prefer to resolve these for a cleaner log with less noise. Two techniques:
Thanks Chris for your reply - it took care of the warnings. I appreciate the help. Now I need to figure out the JSON end of my code.
You're almost there.
libname data json fileref=out;
data result;
set data.result;
run;
(Use the INPUT function to change these fields to the proper types -- numeric, date, etc.)
Hi Chris! I have been trying your suggestions and am still getting an error and no results. I am so close and it is probably a super easy fix, but am missing something. My current code is:
filename out TEMP;
filename cacm TEMP;
proc http
method="GET"
out=out
headerout=cacm
AUTH_NEGOTIATE
WEBUSERNAME="admin"
WEBPASSWORD="xxxxxxxxx";
run;
*libname CACM xlsx "%sysfunc(pathname(out))" ;
*proc copy inlib=cacm outlib=WORK;
*run;
libname result JSON fileref=out;
data result;
set data.result;
run;
I get this error -
ERROR: File DATA.RESULT.DATA does not exist.
I have tried using different names other than "result" for my JSON libname statement and don't seem to get anywhere. Really scratching me head on this one and hoping you or others may see my coding error.
As always, thanks so much for yours (and everyone's) help!
You code is a little different than mine. The JSON libname in your code is RESULT, but in mine I named it DATA. Try using PROC DATASETS to see the contents of the JSON data:
libname data json fileref=out;
proc datasets lib=data; quit;
/* if there is a RESULT table then this should work */
data result;
set data.result;
run;
Hi Chris;
I ran the code and RESULT is not there. My log shows:
libname data json fileref=out;
NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME.
NOTE: Libref DATA was successfully assigned as follows:
Engine: JSON
Physical Name: /saswork/SAS_work344100007187_pgnp008.ABCD.com/#LN00256
proc datasets lib=data;
! quit;
The results show that the libref is DATA with Access of READONLY.
When I use DATA as my file name I get:
ERROR: File DATA.RESULT.DATA does not exist.
Check the contents of your JSON file to ensure it has what you think should be there. Assuming your fileref is named out:
data _null_;
rc = jsonpp("data","log");
run;
Using the data you supplied before, it should look something like this:
Chris, what you sent is what I expected to see as well, but am not getting that anywhere. My current code (I have been trying various filenames during our blogging) looks like:
filename out TEMP;
filename cacm TEMP;
proc http
url='https://ABCD.service-now.com/api/now/table/u_cacm_pl_uw_mngr_type_8?...'
method="GET"
out=out
headerout=cacm
AUTH_NEGOTIATE
WEBUSERNAME="admin"
WEBPASSWORD="xxxxxxx";
run;
libname CACM json "%sysfunc(pathname(out))" ;
proc copy inlib=cacm outlib=WORK;
run;
libname CACM JSON fileref=out;
*data result;
* set data.result;
*run;
data _null_;
rc = jsonpp("data","log");
run;
Log:
41 libname CACM JSON fileref=out;
NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME.
NOTE: Libref CACM was successfully assigned as follows:
Engine: JSON
Physical Name: /saswork/SAS_work36A300006AD9_pgnp008.ABCD.com/#LN00050
42 *data result;
43 * set data.result;
44 *run;
45 data _null_;
46 rc = jsonpp("data","log");
47 run;
ERROR: Physical file does not exist, /config/sas94/Lev1/SASApp/data.
ALLDATA:
P P1 P2 V Value
1 error 0
2 error message 1 User Not Authenticated
2 error detail 1 Required to provide Auth information
1 status 1 failure
My code was wrong - should be:
data _null_;
rc = jsonpp("out","log");
run;
But the ALLDATA member has the messages. Your API call was not authenticated properly. You're trying to use Basic authentication (user/pw). Maybe the user/pw is wrong, or maybe the service isn't configured to allow Basic auth. It's often disallowed these days as OAuth2 is more standard and secure.
At the least, your PROC HTTP statement should use AUTH_BASIC instead of NEGOTIATE, as that's the protocol you want.
Try your API call in another interactive environment to get it working before trying to script it with SAS. Postman is a good one. See tips here.
Chris,
Both you and Reeza have been of great help! I did finally get this JSON to load in to SAS. I am a newbie on some of this and the information you shared is greatly appreciated. The authentication issue has been resolved and the data is coming through exactly as you indicated. You made my day!
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.