BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
adilar39
Obsidian | Level 7

Hi, everyone.

I'd like to ask some question.

 

I've read the guide about how to read JSON data in SAS Studio from this post:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-read-JSON-data-in-SAS/ta-p/849000

I've tried the example there and it works fine.

 

Now I want to try reading data given from Google Maps Distance Matrix API. They gave an example on how their data will be given after user chose the direction, shown here:
https://developers.google.com/maps/documentation/distance-matrix/distance-matrix#json_1

The JSON data would look like this:

{
 
"destination_addresses":
   
["San Francisco, Californie, États-Unis", "Victoria, BC, Canada"],
 
"origin_addresses":
   
["Vancouver, BC, Canada", "Seattle, Washington, États-Unis"],
 
"rows":
   
[
     
{
       
"elements":
         
[
           
{
             
"distance": { "text": "1 712 km", "value": 1711765 },
             
"duration": { "text": "3 jours 16 heures", "value": 318119 },
             
"status": "OK",
           
},
           
{
             
"distance": { "text": "140 km", "value": 139695 },
             
"duration": { "text": "6 heures 49 minutes", "value": 24567 },
             
"status": "OK",
           
},
         
],
     
},
     
{
       
"elements":
         
[
           
{
             
"distance": { "text": "1 452 km", "value": 1451704 },
             
"duration": { "text": "3 jours 2 heures", "value": 266680 },
             
"status": "OK",
           
},
           
{
             
"distance": { "text": "146 km", "value": 146500 },
             
"duration": { "text": "2 heures 53 minutes", "value": 10374 },
             
"status": "OK",
           
},
         
],
     
},
   
],
 
"status": "OK",
}

 My code look like this:

 

filename location temp;
/* Sample JSON from Google Maps's Distance API Matrix */
data _null_;
 file location;
 infile datalines;
 input;
 put _infile_;
datalines;
{
  "destination_addresses":
    ["San Francisco, Californie, États-Unis", "Victoria, BC, Canada"],
  "origin_addresses":
    ["Vancouver, BC, Canada", "Seattle, Washington, États-Unis"],
  "rows":
    [
      {
        "elements":
          [
            {
              "distance": { "text": "1 712 km", "value": 1711765 },
              "duration": { "text": "3 jours 16 heures", "value": 318119 },
              "status": "OK",
            },
            {
              "distance": { "text": "140 km", "value": 139695 },
              "duration": { "text": "6 heures 49 minutes", "value": 24567 },
              "status": "OK",
            },
          ],
      },
      {
        "elements":
          [
            {
              "distance": { "text": "1 452 km", "value": 1451704 },
              "duration": { "text": "3 jours 2 heures", "value": 266680 },
              "status": "OK",
            },
            {
              "distance": { "text": "146 km", "value": 146500 },
              "duration": { "text": "2 heures 53 minutes", "value": 10374 },
              "status": "OK",
            },
          ],
      },
    ],
  "status": "OK",
}
;
run;

libname data JSON fileref=location;
proc datasets lib=data nolist nodetails;
 contents data=_all_;
quit;

Error shown up saying that Object is not complete, even thought there are 40 records were written.

 

1 %studio_hide_wrapper;
83 filename location temp;
84 /* Sample JSON from Google Maps's Distance API Matrix */
85 data _null_;
86 file location;
87 infile datalines;
88 input;
89 put _infile_;
90 datalines;
NOTE: The file LOCATION is:
Filename=/saswork/SAS_workE81900005B44_xxxxx.com/#LN00016,
Owner Name=it3,Group Name=sasusers,
Access Permission=-rw-r--r--,
Last Modified=21/Februari/2024 08:48:34
NOTE: 40 records were written to the file LOCATION.
The minimum record length was 80.
The maximum record length was 80.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
 
131 ;
132 run;
133
134 libname data JSON fileref=location;
NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME.
ERROR: Invalid JSON in input near line 15 column 14: Object is not complete.
ERROR: Error in the LIBNAME statement.
135 proc datasets lib=data nolist nodetails;
ERROR: Libref DATA is not assigned.
136 contents data=_all_;
NOTE: Statements not processed because of errors noted above.
137 quit;
ERROR: Libref DATA is not assigned.
NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
 
138
139 %studio_hide_wrapper;
150
151
Does this mean that data given from Google does not match JSON that can be read by SAS? Or the fault lies in how the code reading the JSON?

Thanks in advance.
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Google has posted invalid JSON in their example.  They have extra commas all over the place which is causing the issue.  I assume it is caused by their using that unreadable style where they place the continuation characters (commas in this case) at the END of the line BEFORE instead of at the START of the continuation line where a human could more easily scan for it and fix it.

options parmcards=json;
filename json temp;
parmcards4;
{
  "destination_addresses": ["Lexington, MA, USA", "Concord, MA, USA"],
  "origin_addresses": ["Boston, MA, USA", "Charlestown, Boston, MA, USA"],
  "rows":
    [
      {
        "elements":
          [
            {
              "distance": { "text": "33.3 km", "value": 33253 },
              "duration": { "text": "27 mins", "value": 1620 },
              "duration_in_traffic": { "text": "34 mins", "value": 2019 },
              "status": "OK"
            },
            {
              "distance": { "text": "41.5 km", "value": 41491 },
              "duration": { "text": "33 mins", "value": 1981 },
              "duration_in_traffic": { "text": "39 mins", "value": 2342 },
              "status": "OK"
            }
          ]
      },
      {
        "elements":
          [
            {
              "distance": { "text": "31.1 km", "value": 31100 },
              "duration": { "text": "26 mins", "value": 1543 },
              "duration_in_traffic": { "text": "29 mins", "value": 1754 },
              "status": "OK"
            },
            {
              "distance": { "text": "39.3 km", "value": 39338 },
              "duration": { "text": "32 mins", "value": 1904 },
              "duration_in_traffic": { "text": "35 mins", "value": 2077 },
              "status": "OK"
            }
          ]
      }
    ],
  "status": "OK"
}
;;;;

libname json json ;
proc copy inlib=json outlib=work; run;

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

Google has posted invalid JSON in their example.  They have extra commas all over the place which is causing the issue.  I assume it is caused by their using that unreadable style where they place the continuation characters (commas in this case) at the END of the line BEFORE instead of at the START of the continuation line where a human could more easily scan for it and fix it.

options parmcards=json;
filename json temp;
parmcards4;
{
  "destination_addresses": ["Lexington, MA, USA", "Concord, MA, USA"],
  "origin_addresses": ["Boston, MA, USA", "Charlestown, Boston, MA, USA"],
  "rows":
    [
      {
        "elements":
          [
            {
              "distance": { "text": "33.3 km", "value": 33253 },
              "duration": { "text": "27 mins", "value": 1620 },
              "duration_in_traffic": { "text": "34 mins", "value": 2019 },
              "status": "OK"
            },
            {
              "distance": { "text": "41.5 km", "value": 41491 },
              "duration": { "text": "33 mins", "value": 1981 },
              "duration_in_traffic": { "text": "39 mins", "value": 2342 },
              "status": "OK"
            }
          ]
      },
      {
        "elements":
          [
            {
              "distance": { "text": "31.1 km", "value": 31100 },
              "duration": { "text": "26 mins", "value": 1543 },
              "duration_in_traffic": { "text": "29 mins", "value": 1754 },
              "status": "OK"
            },
            {
              "distance": { "text": "39.3 km", "value": 39338 },
              "duration": { "text": "32 mins", "value": 1904 },
              "duration_in_traffic": { "text": "35 mins", "value": 2077 },
              "status": "OK"
            }
          ]
      }
    ],
  "status": "OK"
}
;;;;

libname json json ;
proc copy inlib=json outlib=work; run;
adilar39
Obsidian | Level 7
Now that works. So I need to adjust their output before input them inside SAS. Thank you.
Tom
Super User Tom
Super User

@adilar39 wrote:
Now that works. So I need to adjust their output before input them inside SAS. Thank you.

Hopefully the mistake is just in the example in the documentation.

 

Did you have trouble with the results returned from actual queries?

adilar39
Obsidian | Level 7

We're still in the discussion stage about using Google Maps API with our Google Maps local partner. They recently said that they need to confirm first whether they can provide the result like the example shown in the web. If it's possible, we then would discuss more about the format that can be read by SAS.

I ask here first so that when they can provide it, I would be able to test it immediately.

Tom
Super User Tom
Super User

In the little I have done with that API to get distance estimate I have not needed to actually parse the whole JSON text.  Instead I just use normal SAS input functions to find the location where the numbers I want are stored and read them.

 

Typically something like that looks for the keyword of interest and then reads the value that follows:

data want;
  infile 'json.txt' dlm='[{,: }]' ;
  input @'"duration":' @'"value"' duraction ;
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 5 replies
  • 380 views
  • 0 likes
  • 2 in conversation