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

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

 

 
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User

You have JSON Objectives that would mess up the result.

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

higgoboss
Calcite | Level 5

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"

 

 

Ksharp
Super User

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

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

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

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

AhmedAl_Attar
Rhodochrosite | Level 12

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

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 3722 views
  • 0 likes
  • 4 in conversation