topic Re: Process JSON with infile statment in SAS Programming

Process JSON with infile statment

higgoboss — Tue, 22 Nov 2016 10:08:26 GMT

Hi there

I was wondering if someone could help me, I have been using SAS for a good few years but are coming out of my comfort zone on this one. I want to process data from a JSON file that I get from a API call using a filename statement as per the example below.

It seems to work until I try and read in the "comment" field, which I have commented out below. Basically this field only exists for some records which I think is causing the problem as when uncommented, it appears that only records where this is populated come through.

I dont have proc dc2 and I have dabbled with groovey but I dont think I have the extra jar packages and if possible would prefer to go though this approach as, to my shame, have little knowledge of Javascipt.

My code example below

filename api url "https://api.mydata.com/api/responses.json"

DATA example;
INFILE api LRECL = 1000000 SCANOVER encoding="utf-8" truncover dsd dlm="}},{";
INPUT
@'"id":' Response_ID : best32.
@'"time":' Date: $23. 
@'"ip_address":' ip_address: $16.
@'"identity":' identity: $32.
@'"page":' page: $150.
@'"user_agent":' user_agent: $150.
@'"answer":' Main: $100.
/* @'"comment":' comment: $100.*/
@'"SessionID":' session_id: best32.
@'"Language":' Language: $5.
@'"Locale":' Locale: $2.
@'"Channel":' Channel: $20.
@@;
run;

Example of the data I am reading in

{
"id": 89702492,
"time": "2016-10-06 14:26:25 UTC",
"anon_visitor_id": "xxxxxxxxxxxxxxxxxxxxxx",
"ip_address": "111.111.111.111",
"identity": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
"page": "https://secure.hello.com",
"referrer": null,
"user_agent": "Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit",
"nudge_id": 111111,
"nudge_name": "ATTRIBUTION - Hello",
"answered_questions":
{
312102:
{
"question_id": 312102,
"question_title": "what is the time ",
"question_type": "radio",
"answer": "Other please say",
"selected_option_id": 890309,
"comment": "I dont know"
}
 
}
,
 
"properties":
{
"SessionID": "4444444444",
"Language": "fr",
"Locale": "Be",
"Channel": "Affiliate"
}
 
}
,

Thanks so much for your help

Higgy

Re: Process JSON with infile statment

RW9 — Tue, 22 Nov 2016 10:23:55 GMT

If the "comment" variable is not present at all, then yes your import will not work. Here is another solution presented by KSharp.

https://communities.sas.com/t5/SAS-Enterprise-Guide/Reading-json-file-into-SAS-using-SAS-enterprise-guide/td-p/205044

The principal being to import all the data as parameter/response and then transpose it up. Means a little more work in datastep getting the output exactly as you like, but would be more flexible on adding nor parameters or dropping them.

Re: Process JSON with infile statment

Ksharp — Tue, 22 Nov 2016 10:57:18 GMT

You have JSON Objectives that would mess up the result.

What kind of output do you like to see ? post the result dataset.

Re: Process JSON with infile statment

higgoboss — Tue, 22 Nov 2016 11:05:32 GMT

Hi there

Ksharps answer seems to have potential, however in the first datastep

"user_agent": "Mozilla/5.0 (Windows NT 10.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/11.1.1111.111 Safari/111.11",

Comes out as

like Gecko) Chrome/11.1.1111.11 Safari/111.11"

Re: Process JSON with infile statment

Ksharp — Tue, 22 Nov 2016 11:17:00 GMT

OK. This could give you a start .

data x;
infile '/folders/myfolders/json.txt' lrecl=320000 dsd dlm='{},';
input x : $400. @@;
if not missing(x);
run;
data want;
 set x;
 name=dequote(strip(scan(x,1,':','mq')));
 value=dequote(strip(scan(x,-1,':','mq')));
run;

Re: Process JSON with infile statment

higgoboss — Tue, 22 Nov 2016 11:31:41 GMT

Thanks Ksharp - but it doesnt seem to work for user agent field which it puts accross two rows. The original data has a commas after KHTML which I think makes it go to the next row

[

"id":1111111

"time":"2016-11-21 20:41:18 UTC"

"user_agent":"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML

like Gecko) Chrome/11.1.1111.11 Safari/111.11"

"nudge_id":164851

Re: Process JSON with infile statment

Ksharp — Tue, 22 Nov 2016 11:59:50 GMT

OK. Try this one .

data x;
infile '/folders/myfolders/json.txt' lrecl=320000 dsd dlm='{},:[]';
input x : $400. @@;
run;
data want;
 merge x x(firstobs=2 rename=(x=value));
 if lowcase(x) in ("id" "time" "ip_address" "identity" "page" 
"user_agent" "answer" "comment" "sessionid"
 "language" "locale" "channel");
run;




proc print;
run;

Re: Process JSON with infile statment

higgoboss — Tue, 22 Nov 2016 13:00:28 GMT

Thanks so much Sir - that seems to work and is a good little work around !

Wish I had posted on this page earlier - would have saved me hours of time

Thanks again

Higgy

Re: Process JSON with infile statment

AhmedAl_Attar — Tue, 22 Nov 2016 13:07:02 GMT

Higgy,

If previous answers did not provide what you need, you may want to check this blog if you have SAS 9.4,

Using SAS DS2 to parse JSON

Hope this helps,

Ahmed