BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
anu_ha
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

26 REPLIES 26
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
SASJedi
SAS Super FREQ

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
Calcite | Level 5

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
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
anu_ha
Calcite | Level 5

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
Tom
Super User Tom
Super User

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

BrunoMueller
SAS Super FREQ

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
Calcite | Level 5

Thanks Bruno, this works-:)

anu_ha
Calcite | Level 5

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

BrunoMueller
SAS Super FREQ

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
Calcite | Level 5

Hi Bruno,

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

BrunoMueller
SAS Super FREQ

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
SAS Employee

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
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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