Insert JSON data from Google Maps's Distance Matrix API

[adilar39]

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.

[Tom · Accepted solution]

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]

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

[Tom]

---

@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]

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]

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;