Desktop productivity for business analysts and programmers

Reading json file into SAS using SAS enterprise guide

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Reading json file into SAS using SAS enterprise guide

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


Accepted Solutions
Solution
‎06-18-2015 08:21 AM
Grand Advisor
Posts: 9,596

Re: Reading json file into SAS using SAS enterprise guide

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


All Replies
Community Manager
Posts: 2,702

Re: Reading json file into SAS using SAS enterprise guide

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

SAS Employee
Posts: 104

Re: Reading json file into SAS using SAS enterprise guide

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

Occasional Contributor
Posts: 16

Re: Reading json file into SAS using SAS enterprise guide

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;

Community Manager
Posts: 2,702

Re: Reading json file into SAS using SAS enterprise guide

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

Occasional Contributor
Posts: 16

Re: Reading json file into SAS using SAS enterprise guide

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?

connectedToserviceNamecallGuidoriginstateChangedAtsequenceappletNameeventstateduration
"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
Super User
Super User
Posts: 6,380

Re: Reading json file into SAS using SAS enterprise guide

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

SAS Super FREQ
Posts: 676

Re: Reading json file into SAS using SAS enterprise guide

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

Occasional Contributor
Posts: 16

Re: Reading json file into SAS using SAS enterprise guide

Thanks Bruno, this works-Smiley Happy

Occasional Contributor
Posts: 16

Re: Reading json file into SAS using SAS enterprise guide

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

{"count":8029,"calls":[{"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"},{"connectedTo":"08003164071","serviceName":"05 Direct","callGuid":"01456958-bf2e-48ea-b22c-d3f6f5a9a1c2","origin":"02082481633","stateChangedAt":"2014-04-16T07:20:17Z","sequence":1,"appletName":"AN Welcome Message","event":"NewApplet","state":"","additionalParameters":{"applet Type":"Announcement"}},{"connectedTo":"08003164071","serviceName":"05 Direct","callGuid":"01456958-bf2e-48ea-b22c-d3f6f5a9a1c2","origin":"02082481633","stateChangedAt":"2014-04-16T07:20:17Z","sequence":2,"appletName":"SHIM To Customers","event":"NewApplet","state":"","ringDuration":"00:00:00","duration":"00:00:00","additionalParameters":{"applet Type":"Shim"}},{"connectedTo":"08003164071","serviceName":"05 Direct","callGuid":"01456958-bf2e-48ea-b22c-d3f6f5a9a1c2","origin":"02082481633","stateChangedAt":"2014-04-16T07:20:17Z","sequence":3,"appletName":"Dial In Config","event":"NewApplet","state":"","additionalParameters":{"applet Type":"DialInConfig"}},{"connectedTo":"08003164071","serviceName":"05 Direct","callGuid":"01456958-bf2e-48ea-b22c-d3f6f5a9a1c2","origin":"02082481633","stateChangedAt":"2014-04-16T07:20:17Z","sequence":4,"appletName":"RTB Opening Hours","event":"NewApplet","state":"","additionalParameters":{"applet Type":"Router"}},{"connectedTo":"08003164071","serviceName":"05 Direct","callGuid":"01456958-bf2e-48ea-b22c-d3f6f5a9a1c2","origin":"02082481633","stateChangedAt":"2014-04-16T07:20:17Z","sequence":5,"appletName":"TM Out Of Hours","event":"NewApplet","state":"","ringDuration":"00:00:00","duration":"00:00:00","additionalParameters":{"applet Type":"MessageTake"}},{"connectedTo":"08003164071","serviceName":"05 Direct","callGuid":"01456958-bf2e-48ea-b22c-d3f6f5a9a1c2","origin":"02082481633","stateChangedAt":"2014-04-16T07:20:17Z","sequence":0,"appletName":"AN Welcome Message","event":"Start","state":""},{"connectedTo":"01179812753","serviceName":"Dan Pike","callGuid":"01456968-8dc7-4e75-bcd0-1e4a6ffb4a31","origin":"07551170374","stateChangedAt":"2014-04-16T07:36:43Z","sequence":5,"appletName":"TM Dan Pike","event":"End","state":"Caller","duration":"00:01:13"},{"connectedTo":"01179812753","serviceName":"Dan Pike","callGuid":"01456968-8dc7-4e75-bcd0-1e4a6ffb4a31","origin":"07551170374","stateChangedAt":"2014-04-16T07:35:30Z","sequence":1,"appletName":"AN Calls Recorded","event":"NewApplet","state":"","additionalParameters":{"applet Type":"Announcement"}},{"connectedTo":"01179812753","serviceName":"Dan Pike","callGuid":"01456968-8dc7-4e75-bcd0-1e4a6ffb4a31","origin":"07551170374","stateChangedAt":"2014-04-16T07:35:30Z","sequence":2,"appletName":"Record","event":"NewApplet","state":"","ringDuration":"00:00:00","duration":"00:00:00","additionalParameters":{"applet Type":"CallRecording"}},{"connectedTo":"01179812753","serviceName":"Dan Pike","callGuid":"01456968-8dc7-4e75-bcd0-1e4a6ffb4a31","origin":"07551170374","stateChangedAt":"2014-04-16T07:35:30Z","sequence":3,"appletName":"A Dan Pike","event":"NewApplet","state":"","ringDuration":"00:00:00","duration":"00:00:00","additionalParameters":{"applet Type":"ACD"}},{"connectedTo":"01179812753","serviceName":"Dan Pike","callGuid":"01456968-8dc7-4e75-bcd0-1e4a6ffb4a31","origin":"07551170374","stateChangedAt":"2014-04-16T07:35:34Z","sequence":4,"appletName":"TM Dan Pike","event":"NewApplet","state":"","additionalParameters":{"applet Type":"MessageTake"}},{"connectedTo":"01179812753","serviceName":"Dan Pike","callGuid":"01456968-8dc7-4e75-bcd0-1e4a6ffb4a31","origin":"07551170374","stateChangedAt":"2014-04-16T07:35:34Z","sequence":3,"appletName":"A Dan Pike","event":"Queue","state":"Queue State: No Agents","duration":"00:00:00","additionalParameters":{"queue Reason":"ACD","iteration":""}},{"connectedTo":"01179812753","serviceName":"Dan Pike","callGuid":"01456968-8dc7-4e75-bcd0-1e4a6ffb4a31","origin":"07551170374","stateChangedAt":"2014-04-16T07:35:30Z","sequence":0,"appletName":"AN Calls Recorded","event":"Start","state":""},{"connectedTo":"01179817313","serviceName":"Brian Donovan","callGuid":"01456971-bd31-40f0-8785-3a5d28d36475","origin":"07977497865","stateChangedAt":"2014-04-16T07:45:11Z","sequence":3,"appletName":"A Brian Donovan","event":"ACDDelivery","state":"Transfer State: Connected","ringDuration":"00:00:10","duration":"00:04:11","additionalParameters":{"agent Id":"091","group Id":"345345","agent Phone":"01256517573"}},{"connectedTo":"01179817313","serviceName":"Brian Donovan","callGuid":"01456971-bd31-40f0-8785-3a5d28d36475","origin":"07977497865","stateChangedAt":"2014-04-16T07:49:23Z","sequence":4,"appletName":"A Brian Donovan","event":"End","state":"Caller","duration":"00:04:25"},{"connectedTo":"01179817313","serviceName":"Brian Donovan","callGuid":"01456971-bd31-40f0-8785-3a5d28d36475","origin":"07977497865","stateChangedAt":"2014-04-16T07:44:57Z","sequence":1,"appletName":"AN Calls Recorded","event":"NewApplet","state":"","additionalParameters":{"applet Type":"Announcement"}},{"connectedTo":"01179817313","serviceName":"Brian Donovan","callGuid":"01456971-bd31-40f0-8785-3a5d28d36475","origin":"07977497865","stateChangedAt":"2014-04-16T07:44:57Z","sequence":2,"appletName":"Record","event":"NewApplet","state":"","ringDuration":"00:00:00","duration":"00:00:00","additionalParameters":{"applet Type":"CallRecording"}},{"connectedTo":"01179817313","serviceName":"Brian Donovan","callGuid":"01456971-bd31-40f0-8785-3a5d28d36475","origin":"07977497865","stateChangedAt":"2014-04-16T07:44:57Z","sequence":3,"appletName":"A Brian Donovan","event":"NewApplet","state":"","ringDuration":"00:00:00","duration":"00:00:00","additionalParameters":{"applet Type":"ACD"}},{"connectedTo":"01179817313","serviceName":"Brian Donovan","callGuid":"01456971-bd31-40f0-8785-3a5d28d36475","origin":"07977497865","stateChangedAt":"2014-04-16T07:45:02Z","sequence":3,"appletName":"A Brian Donovan","event":"Queue","state":"Queue State: Success","duration":"00:00:00","additionalParameters":{"queue Reason":"ACD","iteration":"0"}},{"connectedTo":"01179817313","serviceName":"Brian Donovan","callGuid":"01456971-bd31-40f0-8785-3a5d28d36475","origin":"07977497865","stateChangedAt":"2014-04-16T07:44:57Z","sequence":0,"appletName":"AN Calls Recorded","event":"Start","state":""},{"connectedTo":"01179817946","serviceName":"Rhiannon Green","callGuid":"01456973-0ddd-4438-9f56-eb41490f2e2d","origin":"01173003658","stateChangedAt":"2014-04-16T07:47:04Z","sequence":5,"appletName":"TM Rhiannon Green","event":"End","state":"Caller","duration":"00:00:44"},{"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"}},{"connectedTo":"01179817946","serviceName":"Rhiannon Green","callGuid":"01456973-0ddd-4438-9f56-eb41490f2e2d","origin":"01173003658","stateChangedAt":"2014-04-16T07:46:20Z","sequence":2,"appletName":"Record","event":"NewApplet","state":"","ringDuration":"00:00:00","duration":"00:00:00","additionalParameters":{"applet Type":"CallRecording"}},{"connectedTo":"01179817946","serviceName":"Rhiannon Green","callGuid":"01456973-0ddd-4438-9f56-eb41490f2e2d","origin":"01173003658","stateChangedAt":"2014-04-16T07:46:20Z","sequence":3,"appletName":"A Rhiannon Green","event":"NewApplet","state":"","ringDuration":"00:00:00","duration":"00:00:00","additionalParameters":{"applet Type":"ACD"}},{"connectedTo":"01179817946","serviceName":"Rhiannon Green","callGuid":"01456973-0ddd-4438-9f56-eb41490f2e2d","origin":"01173003658","stateChangedAt":"2014-04-16T07:46:25Z","sequence":4,"appletName":"TM Rhiannon Green","event":"NewApplet","state":"","additionalParameters":{"applet Type":"MessageTake"}},{"connectedTo":"01179817946","serviceName":"Rhiannon Green","callGuid":"01456973-0ddd-4438-9f56-eb41490f2e2d","origin":"01173003658","stateChangedAt":"2014-04-16T07:46:25Z","sequence":3,"appletName":"A Rhiannon Green","event":"Queue","state":"Queue State: No Agents","duration":"00:00:00","additionalParameters":{"queue Reason":"ACD","iteration":""}},{"connectedTo":"01179817946","serviceName":"Rhiannon Green","callGuid":"01456973-0ddd-4438-9f56-eb41490f2e2d","origin":"01173003658","stateChangedAt":"2014-04-16T07:46:20Z","sequence":0,"appletName":"AN Calls Recorded","event":"Start","state":""}

SAS Super FREQ
Posts: 676

Re: Reading json file into SAS using SAS enterprise guide

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"

            }

        },

Occasional Contributor
Posts: 16

Re: Reading json file into SAS using SAS enterprise guide

Hi Bruno,

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

SAS Super FREQ
Posts: 676

Re: Reading json file into SAS using SAS enterprise guide

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

Trusted Advisor
Posts: 1,300

Re: Reading json file into SAS using SAS enterprise guide

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;

Community Manager
Posts: 2,702

Re: Reading json file into SAS using SAS enterprise guide

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 26 replies
  • 3558 views
  • 5 likes
  • 8 in conversation