Solved: Re: Reading json file into SAS using SAS enterprise guide

anu_ha · Posted 06-12-2015 08:03 AM

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"}}

Ksharp · Posted 06-18-2015 08:21 AM

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

View solution in original post

ChrisHemedinger · Posted 06-12-2015 08:36 AM

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

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.

SASJedi · Posted 06-12-2015 09:10 AM

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

Check out my Jedi SAS Tricks for SAS Users

anu_ha · Posted 06-12-2015 09:31 AM

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;

ChrisHemedinger · Posted 06-12-2015 10:15 AM

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

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.

anu_ha · Posted 06-12-2015 10:28 AM

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

Tom · Posted 06-12-2015 01:58 PM

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

BrunoMueller · Posted 06-12-2015 10:21 AM

Hi

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

anu_ha · Posted 06-12-2015 10:40 AM

Thanks Bruno, this works-:)

anu_ha · Posted 06-12-2015 11:08 AM

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

BrunoMueller · Posted 06-14-2015 04:36 PM

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"

}

},

anu_ha · Posted 06-15-2015 10:27 AM

Hi Bruno,

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

BrunoMueller · Posted 06-16-2015 02:35 AM

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

FriedEgg · Posted 06-16-2015 11:00 AM

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;

ChrisHemedinger · Posted 06-16-2015 11:28 AM

Nice job! PROC LUA is experimental (and unadvertised) in SAS 9.4M2, but it has fixes and documentation in Maint 3 (coming soon).

Paul Tomas, the developer of PROC LUA, demonstrates the feature on this SAS Tech Talk interview:

SAS Tech Talk - Driving SAS with Lua - SAS Global Forum Video Portal

Chris

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.

Code: Program

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away