JSON engine (TS1M4) problems

[lukedu]

Hi there,

I have SAS 9.4 (TS1M4) and I find the following problem for JSON engine:

1. The variable type defined in JSON MAP is ignored. If JSON MAP define as numeric, while JSON DATA shows characters, JSON engine just set value as missing. No warning in SAS log window.

2. The variable length defined in JSON MAP is ignored. If JSON data have length more than defined in JSON MAP, JSON engine can still convert it into SAS data set with length larger than in MAP. If JSON data is missing, the generated SAS data set will have the variable with missing value and length 8, not the length defined in MAP.

3. If JSON data file is big, the SAS JSON engine will run for ever. Here is my test of computational time:

Data file size: 12M Loading time 17.93 seconds

Data file size: 24M Loading time 2:22.39, which is 2.5 minutes

Data file size: 87M Loading time 45:53.82, more than 45 minutes

Data file size: 2.8G Loading time -- not finish for 12 hours

Please confirm the above issues.

It will be very helpful if the next version of JSON engine can handle them. Please let us know the estimated date of the next version release.

Thanks,

Luke

[ChrisHemedinger]

I'm a user of the JSON engine -- but have not stress tested it as much as you have.  It works great for my uses: mostly parsing data from REST APIs.

I find the JSON MAP works as it should to convert char to numbers and dates.  However, if the JSON data doesn't match the type you MAPped, I can see where it wouldn't show an error or warning -- it's not like DATA step in that way.

The JSON engine has had a few hotfixes (like this one).  Make sure you try the latest version.

For the other issues, you should file with SAS Tech Support to make sure the problems are tracked.

[lukedu]

Hi Chris,

Thank you very much for your quick response.

I like the hotfix content, even it is not the solution of my questions. Where should I submit my concerns to SAS Tech Support?

Thanks,

Luke

[AhmedAl_Attar]

Hi Luke,

Like anything in SAS, there are always alternative ways to do the same thing 😉

Here are couple of SAS Global Papers that deals with parsing JSON files

- Efficient extraction of JSON information in SAS® using the SCANOVER function - (SGF 2013)

- Parsing JSON with SAS® in 2017 (SGF 2017)

I wonder what your processing times will look if you use these manual approaches?

The way I look at it,

- Library Engines are developed with very generic scope and wide range applicability

- Custom code can address specific file structure and application need, and you have full controll of execution path and logic

Hope this helps,

Ahmed

[lukedu]

Hi Ahmed,

Thanks for the information.

I appreciate SAS developed JSON engine, just like XML engine, which is very efficient comparing with other tools.

I have tried several tools of parsing JSON data into SAS and my opinion is, JSON engine is the best one.

I also have a lot of experience using SAS XML engine. I realised that SAS XML engine exist several versions. I believe JSON engine should also have the next release with the solution of my problem.

Thanks for your helps.

Luke

[Reeza]

So when should the JSON map be adhered to? 

[lukedu]

Please refer:

http://blogs.sas.com/content/sasdummy/2016/12/02/json-libname-engine-sas/

[Reeza]

@lukedu You can log your idea's here: https://communities.sas.com/t5/SASware-Ballot-Ideas/idb-p/sas_ideas

But as Chris indicated, it's a good idea to log it with SAS Tech Support as well.

[lukedu]

Thanks. Yes I have posted to SAS Tech Support.

[lukedu]

Updates of contacting with SAS Tech Support on May 15th, 2017 Monday:

Hi Chevell,

The important one is the third one. The first and the second ones can be handled by some other SAS techniques.

My guess of the third one is because, JSON engine internally create alldata table, which includes all information from JSON source file. This is different from XML engine. The internal alldata table is very convenient for data mining. But it may take too many computational resource. Is there option that, JSON engine will not create alldata table internally? Hope it is similar with XML engine that, other tables can be created based on MAP definition.

Thanks,

Hello Luke,

I am researching the issues that you have discovered and will get back to you shortly with more information.

Sincerely,

Chevell

[lukedu]

I got response today:

Hello Luke,

I hope to have an update for you shortly. We are still researching the issue.

Sincerely,

Chevell

It looks the problem is isential...

[lukedu · Accepted solution]

Till now I didn't  hear from SAS Tech Support. I guess it is one design problem which can not be solved simply.

As business unit, we must have one solution quickly. I have given up SAS by using Python json modules. The benefit is, we can multithreading python script to accelerate the process. For 2G raw data files, I can use pythong to convert to CSV format ascii file, and then load into SAS in 10 minutes.

So I will close this post as solved, even not in SAS.