topic Invalid JSON in Input Error in SAS Programming

Invalid JSON in Input Error

UdayGuntupalli — Tue, 17 Jul 2018 19:22:25 GMT

All,
Wondering what this error is trying to prompt me towards and how may I fix it.

Objective : Macro making API call and should receive ZIP file in response

Errors from Log:

ERROR: Invalid JSON in input near line 1 column 1: Encountered an illegal character.

ERROR: Error in the LIBNAME statement.

ERROR: Libref IN is not assigned.

SAS CODE:


 filename ProjResp "<MyFileName.zip>"; 
 libname in json "<MyFileName.zip>";
 
 PROC HTTP 
    URL = &URLDes. 
    Method = "get"
    out = ProjResp; 
    headers "Authorization" = "Bearer &AccessToken.";
 Run; 
 
 Proc Contents Data = in._all_; 
 Run; 
 %Mend;

Re: Invalid JSON in Input Error

Reeza — Tue, 17 Jul 2018 19:38:28 GMT

Can you direct a JSON library to a ZIP file? I would think it would need to be pointed at the JSON file directly not the ZIP version. There is a ZIP connection as well to access to the text files directly.

 libname in json "<MyFileName.zip>";

Re: Invalid JSON in Input Error

UdayGuntupalli — Tue, 17 Jul 2018 19:49:33 GMT

@Reeza ,
Would you recommend re-directing it to a txt file ? If yes, can you kindly point me to an example on how to identify/parse the different files that are captured in the response ?

filename ProjResp "<MyFileName.txt>"; 
libname in json "<MyFileName.txt>"; 

  	PROC HTTP 
  		URL = &URLDes. 
  		Method = "get"
  		out = ProjResp; 
  		headers "Authorization" = "Bearer &AccessToken.";
  	Run;

I updated my script as shown above. However, the text file is not readable and am wondering if the normal json parse as follows will be sufficient ?

data want; 
      set in.root; 
run;

Because, I am not sure, how I can separate the different files expected as part of the output. Can you offer a little more guidance or point me to an example. Would appreciate it.

Re: Invalid JSON in Input Error

Reeza — Tue, 17 Jul 2018 19:51:34 GMT

I don't know what you're trying to do though, you just posted some code, didn't actually explain and there's nothing I can run to test things. For JSON I would suggest google Chris Hemidenger and seeing what his blog posts cover. This isn't a file type I work with often enough and I only installed 9.4 TS1M5 recently which has the JSON support.

Re: Invalid JSON in Input Error

Reeza — Tue, 17 Jul 2018 19:59:46 GMT

Sorry, I do see you're hitting an API and returning a ZIP file. That's problematic tbh, a good API should return XML or JSON IMO. Anyways, I do have some code that does this, but it's at home so I'll post it later. If someone else hasn't answered it. But in general you'll need to get the zip file, save it and then process it, which is a bit of a process that you need to define. For example where are you planning to store the files and do you leave them there or delete somehow?

Re: Invalid JSON in Input Error

Reeza — Wed, 18 Jul 2018 16:00:45 GMT

Here's the code I was using, it doesn't work anymore because I think they've changed their naming conventions but the idea is the same I think. This was designed to download a ZIP file from Statistics Canada, store it into the work folder and then read it in. This also assumed that I knew the name of the file inside the ZIP file.


%let CANSIM=02820087;
/* create a name for our downloaded ZIP */
%let ziploc = %sysfunc(getoption(work))/&cansim.-eng.zip;
filename download "&ziploc";
 
/* Download the ZIP file from the Internet*/
proc http
 method='GET'
 url="http://www20.statcan.gc.ca/tables-tableaux/cansim/csv/&cansim.-eng.zip"
 out=download;
run;

filename inzip zip "&ziploc";
filename xl "%sysfunc(getoption(work))/&cansim.-eng-csv";

data _null_;
   /* using member syntax here */
   infile inzip(&cansim.-eng.csv) 
       lrecl=256 recfm=F length=length eof=eof unbuf;
   file   xl lrecl=256 recfm=N;
   input;
   put _infile_ $varying256. length;
   return;
 eof:
   stop;
run;
 
proc import datafile=xl dbms=csv out=confirmed replace;guessingrows=1000000;
run;