topic Re: PROC HTTP error in SAS Programming

PROC HTTP error

luu — Mon, 09 Apr 2018 17:16:46 GMT

I got an error when i run the proc http. I try to get the json file of medicine named "acetaminophen w/codeine #3 tablets". But there is an error when i run the "libname m JSON fileref=resp2;". The error is "ERROR: Invalid JSON in input near line 1 column 1: Encountered an illegal character. ERROR: Error in the LIBNAME statement.". However from the url, i can see there is a valid json file. I do not know why I cannot get it.

filename resp2 temp;
proc http
url="https://rxnav.nlm.nih.gov/REST/approximateTerm.json?term=acetaminophen w/codeine #3 tablets"
method= "GET"
out=resp2;
run;

libname m JSON fileref=resp2;

ERROR: Invalid JSON in input near line 1 column 1: Encountered an illegal character.
ERROR: Error in the LIBNAME statement.

Re: PROC HTTP error

Reeza — Mon, 09 Apr 2018 17:22:30 GMT

What version of SAS are you using? If you're not sure, you can use the code below and check the log. JSON is in the latest version of SAS (9.4 TS1M5 I think, possibly M4)

proc product_status;run;

Re: PROC HTTP error

luu — Mon, 09 Apr 2018 17:26:32 GMT

I'm using 9.4_m5. I can run through the other medicine but not worked for this one.

Re: PROC HTTP error

BillM_SAS — Mon, 16 Apr 2018 14:14:18 GMT

The message indicates that the JSON engine found what it considers an illegal JSON character in the file. Take a look at or post the file returned from running PROC HTTP.

Re: PROC HTTP error

luu — Mon, 16 Apr 2018 15:40:01 GMT

Thank you for you reply!

Yes, you are right, there is an illegal JSON character in the file. But I don't know how to deal with it. I don't know how to see the file generated by proc http. And i post my code. Could you give me further suggestion of it! Thank you very much!

filename med1 temp;
proc http
url="https://rxnav.nlm.nih.gov/REST/approximateTerm.json?term=acetaminophen w/codeine #3"
method= "GET"
out=med1;
run;
libname resp1 JSON fileref=med1;
data Approximategroup_candidate;
set resp1.Approximategroup_candidate;
run;

Re: PROC HTTP error

BillM_SAS — Mon, 16 Apr 2018 17:27:37 GMT

By specifying TEMP for the file location in the filename statement, the output from running PROC HTTP is sent to a temporary file in the SAS temporary location. You could look at the temporary file by using this macro:

%macro fileecho(fn);
data _NULL_;
infile &fn length=L;
input @1 line $varying32767. L;
put @1 line $varying32767. L;
run;
%mend fileecho;

%fileecho(med1);

Or, you could specify a file name in the FILENAME statement to write the HTTP output to a permanent file:

FILENAME med1 './procHttpOutput.out';

But when I run the code you posted I see the following in the SAS Log window:

NOTE: 400 Bad Request

When I then look at the output from the PROC HTTP, I see an error message in XML format:

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>400 Bad Request</title>
</head><body>
<h1>Bad Request</h1>
<p>Your browser sent a request that this server could not understand.<br />
</p>
<hr>
<address>Apache/2.4.6 (Red Hat Enterprise Linux) OpenSSL/1.0.2k-fips mod_perl/2.0.9 Perl/v5.22.1 S
erver at mor.nlm.nih.gov Port 443</address>
</body></html>

The XML formatted message will definitely result in a illegal character message from the JSON engine. Since this works with other drugs, I suspect that there is a problem with how you are specifying the drug name. Did the drug names that worked include spaces in the drug name specified?

Re: PROC HTTP error

BillM_SAS — Mon, 16 Apr 2018 17:44:13 GMT

It works by excluding the number sign, "#", from the drug name:

73   proc http
74   url="https://rxnav.nlm.nih.gov/REST/approximateTerm.json?term=acetaminophen w/ codeine 3"
75   method= "GET"
76   out=med1;
77   run;

NOTE: PROCEDURE HTTP used (Total process time):
      real time           0.31 seconds
      cpu time            0.01 seconds

NOTE: 200 OK

78   %fileecho(med1);

NOTE: The infile MED1 is:
      Filename=C:\Users\sasbim\AppData\Local\Temp\SAS Temporary Files\_TD15416_d10b742_\#LN00010,
      RECFM=V,LRECL=32767,File Size (bytes)=576,
      Last Modified=16Apr2018:13:39:38,
      Create Time=16Apr2018:13:18:32

{"approximateGroup":{"inputTerm":"acetaminophen w/ codeine 3","comment":"","candidate":[{"rxcui":"
817579","rxaui":"3297609","score":"50","rank":"1"},{"rxcui":"817579","rxaui":"798172","score":"50"
,"rank":"1"},{"rxcui":"817579","rxaui":"798171","score":"50","rank":"1"},{"rxcui":"817579","rxaui"
:"3095428","score":"50","rank":"1"},{"rxcui":"817579","rxaui":"3095427","score":"50","rank":"1"},{
"rxcui":"817579","rxaui":"2626344","score":"50","rank":"1"},{"rxcui":"817579","rxaui":"3076140","s
core":"50","rank":"1"},{"rxcui":"1945644","rxaui":"798175","score":"50","rank":"1"}]}}
NOTE: 1 record was read from the infile MED1.
      The minimum record length was 576.
      The maximum record length was 576.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
79   libname resp1 JSON fileref=med1;
NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME.
NOTE: Libref RESP1 was successfully assigned as follows:
      Engine:        JSON
      Physical Name: C:\Users\sasbim\AppData\Local\Temp\SAS Temporary
      Files\_TD15416_d10b742_\#LN00010
80   data Approximategroup_candidate;
81   set resp1.Approximategroup_candidate;
82   run;

NOTE: There were 8 observations read from the data set RESP1.APPROXIMATEGROUP_CANDIDATE.
NOTE: The data set WORK.APPROXIMATEGROUP_CANDIDATE has 8 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

Re: PROC HTTP error

luu — Mon, 16 Apr 2018 18:51:23 GMT

Thank you very much for your explanation! It is very helpful!

I guess it is the # char make the error because the other records can run without any problem. But I cannot change the original data. My work is cleaning these messy records to get the matched information from the website.

So is there any way to detect these error and write a if condition statement to avoid running these kinds of records?

I try to use "syserr" to prevent this error but it is not worked. Do you have any suggestion about it? Thanks again!

Re: PROC HTTP error

BillM_SAS — Mon, 16 Apr 2018 19:35:02 GMT

There is an example in the documentation:

HTTP Procedure Documentation

Example 14: Using the Status Reporting Macro Variables

Re: PROC HTTP error

luu — Mon, 16 Apr 2018 19:38:32 GMT

Great! I will see these documents and let you know the results! Thanks again for your great help!!!

Re: PROC HTTP error

luu — Fri, 27 Apr 2018 21:50:15 GMT

I tried to rewrite the code again, and the SYS_PROCHTTP_STATUS_CODE can solve my problem! Thank you very much!