Apparent symbolic reference URL

[gregor1]

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!

[Reeza]

What version of SAS do you have? If you have a version beyond 9.4M4+ then you can use the JSON libname very easily.
This blog post illustrates how to use it.
https://blogs.sas.com/content/sasdummy/2016/12/02/json-libname-engine-sas/

FYI - to avoid the warnings put your URL in single quotes. & and % are part of the macro syntax and SAS is thinking you're using macro variables/macros when you're not. If you put them in single quotes it will not try and resolve them and you will not get warnings.

[gregor1]

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.

[Reeza]

You can check your SAS version with the following:

proc product_status;run;

[gregor1]

I am using 9.4_M5 and am trying to use the JSON engine.

[ChrisHemedinger]

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:

• use single quotes in the URL= option on PROC HTTP.  This prevents SAS from trying to process the & parameters as a macro symbol.
• or...especially if you actually do have some macro variables in your URL string (I often do), you can wrap the non-macro params with %str() like so:
  
  "https://abc.service-now.com/api/now/table/u_cacm_pl_uw_mngr_type_8%str(&)sysparm_query=cref_referred..."

[gregor1]

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.

[ChrisHemedinger]

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.)

[gregor1]

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

url='https://abcd.service-now.com/api/now/table/u_cacm_pl_uw_mngr_type_8?sysparm_query=cref_referred_date...'

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!

[ChrisHemedinger]

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;

[gregor1]

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.

[ChrisHemedinger]

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:

[gregor1]

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

[ChrisHemedinger · Accepted solution]

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.

[gregor1]

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!