Unfortunately yes, that is my answer. I just talked to SAS and the JSON engine specialist says it cannot read that type of file.
Hopefully they have this on their to do list as it seems like only a small update would be needed to handle the shifting to a new line, As I mentioned, if I have a file with just one line, the JSON engine works perfectly, it just doesn't know what to do after it hit's the end of the first line.
That's a bit peculiar, most of the json files I have seen are formatted nicely to view in a text editor, so have end of lines. Is there perhaps a non printed or special character in there? Maybe something happened when transferring the file, maybe unix eof on windows or something like that. Really stabbing in the dark here, I can find nothing on end of line for json causing an issue. Can you perhaps post a few lines of the file, or the file.
Well, looking at those lines, one thing jumped out at me in the row:
"$initial_ref... (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.167 Safari/537.36"
Doesn't appear to be a parameter:reponse. Other than that I don't see a problem.
Maybe someone else can chime in here.
Click the gear icon in the top right of the post, you should be able to edit your own posts.
Ahh, I was in some strangely formatted version of the site and wasn't seeing the gear icon. After closing my browser and reopening it, the site looks like it used to.
Anyway, it seems like that the return is something called JSONL, which the JSON engine seems to have issues with. I'm wondering if there's some type of option in maybe a custom mapping file to let it know it's JSONL vs JSON.
Ah, well that might be your answer. JSONL appears to be a slight deviation from JSON, so its possible the JSON engine can't read it directly.
You may need to read it some other way.
Unfortunately yes, that is my answer. I just talked to SAS and the JSON engine specialist says it cannot read that type of file.
Hopefully they have this on their to do list as it seems like only a small update would be needed to handle the shifting to a new line, As I mentioned, if I have a file with just one line, the JSON engine works perfectly, it just doesn't know what to do after it hit's the end of the first line.
I've seen these types of JSONL files before -- you could use SAS DATA step to pass through the file and break up the larger collection into individual files, then point the JSON engine at each of those. Then rejoin the data in DATA step or SQL.
That's an interesting thought. Reading it in line by line, outputting each line to a temp file then using the JSON engine to read that file and append it to the current dataset. Only issue I can see is making sure I create a map file that has the max length of each field.
I may give this a shot. It sounds a lot easier that parsing the lines myself.
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!
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.