Error reading JSON output in SAS
I am trying to parse data from a JSON file into SAS but I am stuck with my code. I cannot post all the JSON content because it is too long, but I can post part of it that is relevant. Here is my SAS code below; when i run it i get something in the log that says "LOST CARD" and i'm not sure what that means.
Some of the code is commented out because the code really works until I go to the variable list ... I wonder ... this error ("LOST CARD") has something to do with "lrecl", the line and length of certain variables ?
Thanks in advance for any help you could provide!
SAS Code:
filename otp1 '...\output\otp1.json';
data work.otp1sas;
infile otp1 lrecl = 32000 /*truncover*/ scanover;
input
@'"startTime":' startTime $20.
@'"endTime":' endTime $20.
@'"walkTime":' walkTime $20.
@'"transitTime":' transitTime $20.
/*@'"waitingTime":' waitingTime $20.
@'"walkDistance":' walkDistance $20.
@'"walkLimitExceeded":' walkLimitExceeded $20.
@'"transfers":' transfers $20.*/;
startTime = scan(startTime,1,',"');
endTime = scan(endTime,1,',"');
walkTime = scan(walkTime,1,',"');
transitTime = scan(transitTime,1,',"');
/*waitingTime = scan(waitingTime,1,',"');
walkDistance = scan(walkDistance,1,',"');
walkLimitExceeded = scan(walkLimitExceeded,1,',"');
transfers = scan(transfers,1,',"');*/
run;
JSON content (related to the above code):
{"requestParameters":{"date":"03-14-2014","mode":"TRANSIT,WALK","arriveBy":"false","showIntermediateStops":"false","fromPlace":"33.8134605,-84.34973148","itinIndex":"0","toPlace":"33.80882004,-84.39769799","time":"10:00am","maxWalkDistance":"3218.688"},"plan":{"date":1394805600000,"from":{"name":"Arlington Avenue Northeast","stopId":null,"stopCode":null,"platformCode":null,"lon":-84.34880165944314,"lat":33.81255118156955,"arrival":null,"departure":null,"orig":null,"zoneId":null,"stopIndex":null},"to":{"name":"Collier Road Northwest","stopId":null,"stopCode":null,"platformCode":null,"lon":-84.39787235641106,"lat":33.808417712941896,"arrival":null,"departure":null,"orig":null,"zoneId":null,"stopIndex":null},"itineraries":[{"duration":2630.0,"startTime":1394806613000,"endTime":1394809243000,"walkTime":994,"transitTime":1273,"waitingTime":363,"walkDistance":1280.0733818655874,"walkLimitExceeded":false,"elevationLost":0.0,"elevationGained":0.0,"transfers":2,"
+3
source to share
2 answers
If you are missing SAS 9.3+ you can use the following
filename otp1 '...\output\otp1.json';
data work.otp1sas;
format
equation $60.
variable $32.
value $32.
startTime $20.
endTime $20.
walkTime $20.
transitTime $20.
waitingTime $20.
walkDistance $20.
walkLimitExceeded $20.
transfers $20.;
** read the JSON and isolate the relevant object **;
infile otp1 lrecl = 32000 truncover ;
input @'"itineraries":[' jsonArray $32000.;
jsonArray = scan(jsonArray, 1, ']');
** separate the objects **;
do objNr = 1 to countw(jsonArray, '}{');
jsonObject = strip(scan(jsonArray, objNr, '}{'));
** separate the equations **;
do varNr = 1 to countw(jsonObject, ',');
equation = strip(scan(jsonObject, varNr, ','));
variable = scan(equation, 1, '"');
value = substr(equation, index(equation, ':') + 1);
** handle cases where a string value contains a comma **;
do while (varNr LT countw(jsonObject,',')
and substr(value, 1, 1) EQ '"'
and substr(value, length(value), 1) NE '"' );
varNr = varNr + 1;
value = trim(value) ||','|| strip(scan(jsonObject, varNr, ','));
end;
select (variable);
when ('startTime') startTime = dequote(value);
when ('endTime') endTime = dequote(value);
when ('walkTime') walkTime = dequote(value);
when ('transitTime') transitTime = dequote(value);
when ('waitingTime') waitingTime = dequote(value);
when ('walkDistance') walkDistance = dequote(value);
when ('walkLimitExceeded') walkLimitExceeded = dequote(value);
when ('transfer') transfer = dequote(value);
otherwise put varNr= variable= value=;
end;
end;
if countw(jsonObject, ',') GT 1 then output;
end;
run;
0
source to share