Reading json file into SAS using SAS enterprise guide

[FriedEgg]

Yes, it is definitely full of quirks in 9.4M2, I for one and looking forward to seeing it improve.

[BrunoMueller]

FriedEgg, Many thanks for the great example.

Bruno

[anu_ha]

Thanks for the Lua example, unfortunately I don't have 9.4 and can not use groovy with restriction around installation. Let me know if anyone has suggestion around the lost card that might solve the problem.

[Ksharp · Accepted solution]

First of all, Your JSON is not right , you missed ']}' at the end of JSON.

And the most simple way is using online parse website to parse JSON . Like :  http://json.cn/

And the following is solution by SAS data step . Hope this could help you .

Code: Program

data have;
infile '/folders/myfolders/have.json' recfm=n dlm='{}[],';
input value : $200. @@;
if value in: ('"count"' '"calls"' '"additionalParameters"') then delete;
run;


data temp;
 set have;
 length name v $ 100;
 if value =: '"connectedTo"' then n+1;
 name=scan(value,2,'"','m');
 v=scan(value,-2,'"','m');
 drop value;
run;
proc transpose data=temp out=want(drop=_name_ n) ;
by n;
var v;
id name;
run;

Xia Keshan

[anu_ha]

I had given those records as sample so the file was not complete.

Thanks for your reply and the solution. It works with my file so really appreciate it!

[Ksharp]

Here is an example about how to import JSON.

http://blogs.sas.com/content/sascom/2013/12/12/how-to-import-twitter-tweets-in-sas-data-step-using-o...

[anu_ha]

I can not use proc groovy I can't install the required jar files.

[FriedEgg]

In newer versions of SAS the required jar files are typically included in the installation's versioned jar repository, but not necessarily used as the default.  For instance, I have a Windows installation 9.4TS1M2 and it uses groovy-all.jar of version ~1.7 that does not included the groovy.json package.  However, my installation has a package of groovy-all version ~2.1 which does include it.  I can then use this newer version by doing the following:

proc groovy;

add sasjar="groovy_2.1.3" version="2.1.3.0_SAS_20130517000930"; /*this exact version can different from product-to-product and by OS, etc...*/

submit;

import groovy.json.JsonSlurper

def slurper = new JsonSlurper()

def result = slurper.parseText('{"person":{"name":"Guillaume", "age":33, "pets":["dog", "cat"]}}')

assert result.person.name == "Guillaume"

assert result.person.age == 33

assert result.person.pets.size() == 2

assert result.person.pets[0] == "dog"

assert result.person.pets[1] == "cat"

endsubmit;

quit;

So long as you have internet access you can also download any necessary JAR to the work directory for use in your process...

[ChrisHemedinger]

However, PROC GROOVY requires XCMD privileges.  The default of NOXCMD in a typical SAS server configuration would prevent PROC GROOVY from working.

Chris

[FriedEgg]

This is indeed true, however, by the OP's comment, he can execute PROC GROOVY, but believe he is unable to 'install' the necessary JAR files.  A JSON package for DS2 is very exciting and something I've been looking forward to since it was pre-production.  Wondering now how it will play with the HTTP package and streaming content.  Parsing a JSONstring would also be possible with PROC LUA (another very exciting new/shiny thing), but with no OS access from that procedure (at this time) you would not be able to read from a text file directly, however, with effort, you could go directly from JSON string to SAS Data file.

[ChrisHemedinger]

For completeness, I'm updating this post with the latest info: a JSON libname engine is now in Base SAS 9.4 (maint 4).

Example:

filename resp temp;
 
/* Neat service from Open Notify project */
proc http 
 url="http://api.open-notify.org/astros.json"
 method= "GET"
 out=resp;
run;
 
/* Assign a JSON library to the HTTP response */
libname space JSON fileref=resp;
 
/* Print result, dropping automatic ordinal metadata */
title "Who is in space right now? (as of &sysdate)";
proc print data=space.people (drop=ordinal:);
run;

[AhmedAl_Attar]

Check this SAS Global Forum paper http://www.lexjansen.com/wuss/2013/103_Paper.pdf.

It has SAS code that you can run within SAS EG to read Twitter JSON Feed.

Hope this helps,

Ahmed