DATA Step, Macro, Functions and more

Process JSON with infile statment

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Process JSON with infile statment

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

 

 

Accepted Solutions
Solution
‎11-28-2016 04:15 AM
Super User
Posts: 9,682

Re: Process JSON with infile statment

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,406

Re: Process JSON with infile statment

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

 

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.

Super User
Posts: 9,682

Re: Process JSON with infile statment

You have JSON Objectives that would mess up the result.

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

New Contributor
Posts: 4

Re: Process JSON with infile statment

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"

 

 

Super User
Posts: 9,682

Re: Process JSON with infile statment

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;
New Contributor
Posts: 4

Re: Process JSON with infile statment

[ Edited ]

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

 

x
[
"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
Solution
‎11-28-2016 04:15 AM
Super User
Posts: 9,682

Re: Process JSON with infile statment

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;
New Contributor
Posts: 4

Re: Process JSON with infile statment

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

Regular Contributor
Posts: 213

Re: Process JSON with infile statment

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

 

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 335 views
  • 0 likes
  • 4 in conversation