topic Re: Reading json file into SAS using SAS enterprise guide in SAS Enterprise Guide

Reading json file into SAS using SAS enterprise guide

anu_ha — Fri, 12 Jun 2015 12:03:23 GMT

Hi,

I am trying to read a json file which, looks like below into SAS using SAS EG. I am using infile statement but not much success. Please share any code if you have, I can not use proc groivy as I don't have admind rights to install the jar etc.

{"count":8141,"calls":[{"connectedTo":"01179817787","serviceName":"05 Direct","callGuid":"014cc38e-1ac5-44ee-8fdc-1176b9d83632","origin":"","stateChangedAt":"2015-04-17T00:19:25Z","sequence":5,"appletName":"TM Out Of Hours","event":"End","state":"Caller","duration":"00:01:13"},{"connectedTo":"01179817787","serviceName":"05 Direct","callGuid":"014cc38e-1ac5-44ee-8fdc-1176b9d83632","origin":"","stateChangedAt":"2015-04-17T00:18:12Z","sequence":1,"appletName":"AN Welcome Message","event":"NewApplet","state":"","ringDuration":"00:00:00","duration":"00:00:00","additionalParameters":{"applet Type":"Announcement"}}

Re: Reading json file into SAS using SAS enterprise guide

ChrisHemedinger — Fri, 12 Jun 2015 12:36:02 GMT

If you want to grab just a few fields from the JSON data, you can use a parsing technique with regular expressions. I've got an example of that uses the LinkedIn REST API to bring data into SAS.

Another technique is to use DS2, which allows for some more flow control in the processing. shared an example on this blog post. He uses the SCAN function (which you can also use in regular ol' DATA step).

Chris

Re: Reading json file into SAS using SAS enterprise guide

SASJedi — Fri, 12 Jun 2015 13:10:29 GMT

And DS2 continues to gain capability in this area. In SAS9.4M3 (coming this summer), DS2 will include a JSON package to facilitate both reading and writing JSON. I'm very much looking forward to that!

May the SAS be with you!
Mark

Re: Reading json file into SAS using SAS enterprise guide

anu_ha — Fri, 12 Jun 2015 13:31:19 GMT

Hi Chris,

I have a json file in .json format, I am not trying to read the linkedin or twitter feed. Below is my attempt at the code. Let me know if anything needs corrected here

data TEST;

INFILE "c:\test.json" LRECL = 3456677 TRUNCOVER SCANOVER

dsd ;

INPUT @'"connectedTo":' connectedTo $50.

@'"serviceName":' serviceName $50.

@'"callGuid":' callGuid $50.

@'"origin":' origin $50.

@'"stateChangedAt":' stateChangedAt $50.

@'"sequence":' sequence $50.

@'"appletName":' appletName $50.

@'"event":' event $50.

@'"state":' state $50.

@'"duration":' duration $50. @@;

run;

Re: Reading json file into SAS using SAS enterprise guide

Ksharp — Fri, 12 Jun 2015 13:37:12 GMT

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-oauth-2-authentication-style/

Re: Reading json file into SAS using SAS enterprise guide

ChrisHemedinger — Fri, 12 Jun 2015 14:15:30 GMT

Is that working for you? You might have more luck with a positional SCAN.

data TEST;
INFILE "c:\temp\test.json" LRECL = 32767  
   TRUNCOVER SCANOVER 
   dsd; 

   length connectedTo $ 50 serviceName $ 50; 
   input; 
   connectedTo = scan(_infile_,6,'".,:{}');
   serviceName = scan(_infile_,8,'".,:{}');

   /* and so on */
run;

Chris

Re: Reading json file into SAS using SAS enterprise guide

BrunoMueller — Fri, 12 Jun 2015 14:21:53 GMT

Have a look at the example below, it can read your json string quite well.

I guess the key is to have modified list input (the colon before the informat name) together with the DLM= option to say data values are delimited either by a , or a }

filename sugus dataurl 'data:
  {
  "count":8141,
  "calls":[
  {
  "connectedTo":"01179817787",
  "serviceName":"05 Direct",
  "callGuid":"014cc38e-1ac5-44ee-8fdc-1176b9d83632",
  "origin":"",
  "stateChangedAt":"2015-04-17T00:19:25Z",
  "sequence":5,
  "appletName":"TM Out Of Hours",
  "event":"End",
  "state":"Caller",
  "duration":"00:01:13"
  },
  {
  "connectedTo":"123456789",
  "serviceName":"05 Direct2",
  "callGuid":"014cc38e-1ac5-44ee-8fdc-1176b9d83632",
  "origin":"",
  "stateChangedAt":"2015-04-17T00:18:12Z",
  "sequence":1,
  "appletName":"AN Welcome Message",
  "event":"NewApplet",
  "state":"",
  "ringDuration":"00:00:00",
  "duration":"00:00:00",
  "additionalParameters":{
  "applet Type":"Announcement"
  }
  }
  ]
  }'
;

data TEST;
  INFILE sugus LRECL = 3456677  TRUNCOVER SCANOVER
    dsd
    dlm=",}"

  ;
  INPUT
    @'"connectedTo":' connectedTo : $50.
    @'"serviceName":' serviceName : $50.
    @'"callGuid":' callGuid : $50.
    @'"origin":' origin : $50.
    @'"stateChangedAt":' stateChangedAt : $50.
    @'"sequence":' sequence : $50.
    @'"appletName":' appletName : $50.
    @'"event":' event : $50.
    @'"state":' state : $50.
    @'"duration":' duration : $50.
    @@;
run;

Bruno

Re: Reading json file into SAS using SAS enterprise guide

anu_ha — Fri, 12 Jun 2015 14:28:12 GMT

Well no, which is why I posted the question. Below are 2 sample records my program can read. Looks like I need some delimiter somewhere.

Will it help if I email the file?

connectedTo	serviceName	callGuid	origin	stateChangedAt	sequence	appletName	event	state	duration
"01179817787","serviceName":"05 Direct","callGuid"	"05 Direct","callGuid":"014cc38e-1ac5-44ee-8fdc-11	"014cc38e-1ac5-44ee-8fdc-1176b9d83632","origin":""	"","stateChangedAt":"2015-04-17T00:18:12Z","sequen	"2015-04-17T00:18:12Z","sequence":4,"appletName":"	0,"appletName":"AN Welcome Message","event":"Start	"TM Out Of Hours","event":"End","state":"Caller","	"NewApplet","state":"","ringDuration":"00:00:00","	"","additionalParameters":{"applet Type":"Shim"}},	"00:00:00","additionalParameters":{"applet Type":"
"01179817787","serviceName":"05 Direct","callGuid"	"05 Direct","callGuid":"014cc4da-a830-4754-8046-34	"014cc559-214c-4c22-9d52-57417a0481f8","origin":""	"","stateChangedAt":"2015-04-17T03:26:41Z","sequen	"2015-04-17T03:26:41Z","sequence":2,"appletName":"	3,"appletName":"Dial In Config","event":"NewApplet	"RTB Opening Hours","event":"NewApplet","state":""	"NewApplet","state":"","additionalParameters":{"ap	""},{"connectedTo":"01179817787","serviceName":"05	"00:01:17"},{"connectedTo":"01179817787","serviceN

Re: Reading json file into SAS using SAS enterprise guide

anu_ha — Fri, 12 Jun 2015 14:28:51 GMT

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

Re: Reading json file into SAS using SAS enterprise guide

anu_ha — Fri, 12 Jun 2015 14:40:09 GMT

Thanks Bruno, this works-:)

Re: Reading json file into SAS using SAS enterprise guide

anu_ha — Fri, 12 Jun 2015 15:08:51 GMT

I think the file has few additional fields, which I am not able to get which is why probably I get the Lost card message!

Below I am trying to put more records

Re: Reading json file into SAS using SAS enterprise guide

Tom — Fri, 12 Jun 2015 17:58:23 GMT

Why did you include TRUNCOVER option? Doesn't the SCANOVER option override that?

Re: Reading json file into SAS using SAS enterprise guide

BrunoMueller — Sun, 14 Jun 2015 20:36:14 GMT

You get the lost card, because not all the "records" have all the fields. The sample code provided runs fine, as long as all the "records" have the same fields. Maybe there is a way to add additional logic to find out whether a field exists or not.

This is a sample record

{

"connectedTo": "08003164071",

"serviceName": "05 Direct",

"callGuid": "01456958-bf2e-48ea-b22c-d3f6f5a9a1c2",

"origin": "02082481633",

"stateChangedAt": "2014-04-16T07:20:22Z",

"sequence": 6,

"appletName": "TM Out Of Hours",

"event": "End",

"state": "Caller",

"duration": "00:00:04"

Record with missing fields

{

"connectedTo": "01179817946",

"serviceName": "Rhiannon Green",

"callGuid": "01456973-0ddd-4438-9f56-eb41490f2e2d",

"origin": "01173003658",

"stateChangedAt": "2014-04-16T07:46:20Z",

"sequence": 1,

"appletName": "AN Calls Recorded",

"event": "NewApplet",

"state": "",

"additionalParameters":

{

"applet Type": "Announcement"

}

Re: Reading json file into SAS using SAS enterprise guide

anu_ha — Mon, 15 Jun 2015 14:27:53 GMT

Hi Bruno,

Could you suggest how can I use a logic to read this file? Will the additional "{" create a problem?

Re: Reading json file into SAS using SAS enterprise guide

FriedEgg — Mon, 15 Jun 2015 16:07:57 GMT

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...

Re: Reading json file into SAS using SAS enterprise guide

ChrisHemedinger — Mon, 15 Jun 2015 16:13:08 GMT

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

Chris

Re: Reading json file into SAS using SAS enterprise guide

AhmedAl_Attar — Mon, 15 Jun 2015 16:22:24 GMT

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

Re: Reading json file into SAS using SAS enterprise guide

FriedEgg — Mon, 15 Jun 2015 16:23:14 GMT

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.

Re: Reading json file into SAS using SAS enterprise guide

BrunoMueller — Tue, 16 Jun 2015 06:35:45 GMT

Currently I can not think of a simple solution to get around the lost card message. As someone already mentioned there are pure Lua based JSON parsers, maybe this will work together with Proc LUA.

Bruno

Re: Reading json file into SAS using SAS enterprise guide

FriedEgg — Tue, 16 Jun 2015 15:00:01 GMT

Here is a quickly thrown together example for PROC LUA. It is 'incomplete' so far as I do not take it any further than decoding the JSON file to a LUA table.

filename foo temp;

data _null_;

file foo;

put '{

"count":8141,

"calls":[

{"connectedTo":"01179817787",

"serviceName":"05 Direct",

"callGuid":"014cc38e-1ac5-44ee-8fdc-1176b9d83632",

"origin":"",

"stateChangedAt":"2015-04-17T00:19:25Z",

"sequence":5,

"appletName":"TM Out Of Hours",

"event":"End",

"state":"Caller",

"duration":"00:01:13"

{"connectedTo":"01179817787",

"serviceName":"05 Direct",

"callGuid":"014cc38e-1ac5-44ee-8fdc-1176b9d83632",

"origin":"",

"stateChangedAt":"2015-04-17T00:18:12Z",

"sequence":1,

"appletName":"AN Welcome Message",

"event":"NewApplet",

"state":"",

"ringDuration":"00:00:00",

"duration":"00:00:00",

"additionalParameters":

{"applet Type":"Announcement"

}

}]}';

run;

proc fcmp outlib=work.func.luaio;

/*sas.fget does not currently work. the returned variable appears to always be nil*/

/*fileget in lua will circumvent this issue for now called as sas.fileget*/

function fileget(fid, len) $;

length c $ 32767;

rc = fget(fid, c, len);

return (putc(c, cats('$',len,'.')));

endsub;

run;

options cmplib=work.func;

filename LuaPath "%sysfunc(pathname(work,l))";

filename json "%sysfunc(pathname(LuaPath,f))/json.lua";

proc http method='get' url="https://raw.githubusercontent.com/FriedEgg/json4lua/master/json/json.lua" out=json; run;

/*forked on Github of - (https://github.com/craigmj/json4lua), by Craig Mason-Jones to make LUA 5.2 compat. Switch global loadstring to load.*/

proc lua infile="json";

submit;

json = require('json')

if sas.fileexist("foo") then

local fid = sas.fopen("foo", "s", 200, "B")

local s = "";

local c = "";

while sas.fread(fid) == 0 do

c = sas.fileget(fid, 200)

s = s .. c

end

rc = sas.fclose(fid)

decoded = json.decode(s)

for k in pairs(decoded["calls"][1]) do print(k) end

else

print(sas.sysmsg())

end

endsubmit;

run;