Parsing JSON in a SAS field

Question

Does SAS have a simple way to parse JSON data within a SAS column yet? I know it can be done with the json object in proc ds2 but that's an awful approach. I'm looking for something that makes it trivial like the JSON libname option but without having to dump everything to a temporary file first.

Answer 1

There is json engine in libname statement(read here). But for a start, you need to change input dataset with condition that it is one json:

Have dataset:

data have;
length str $ 500;
str="{""method"":""Get"",""parameters"":{""int"":1,""string"":""teststring1""}}";
output;    
str="{""method"":""Get"",""parameters"":{""int"":2,""string"":""teststring2""}}";
output;    
str="{""method"":""Get"",""parameters"":{""int"":3,""string"":""teststring3""}}";  
output;    
str="{""method"":""Get"",""parameters"":{""int"":4,""string"":""teststring4""}}";
output;
str="{""method"":""Get"",""parameters"":{""int"":5,""string"":""teststring5""}}";
output;
str="{""method"":""Get"",""parameters"":{""int"":6,""string"":""teststring6""}}";
output;
str="{""method"":""Get"",""parameters"":{""int"":7,""string"":""teststring7""}}";
output;
str="{""method"":""Get"",""parameters"":{""int"":8,""string"":""teststring8""}}";
output;
str="{""method"":""Get"",""parameters"":{""int"":9,""string"":""teststring9""}}";
output;
str="{""method"":""Get"",""parameters"":{""int"":10,""string"":""teststring10""}}";
output;
run;

+==================================================================+
|                               str                                |
+==================================================================+
| {"method":"Get","parameters":{"int":1,"string":"teststring1"}}   |
+------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":2,"string":"teststring2"}}   |
+------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":3,"string":"teststring3"}}   |
+------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":4,"string":"teststring4"}}   |
+------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":5,"string":"teststring5"}}   |
+------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":6,"string":"teststring6"}}   |
+------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":7,"string":"teststring7"}}   |
+------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":8,"string":"teststring8"}}   |
+------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":9,"string":"teststring9"}}   |
+------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":10,"string":"teststring10"}} |
+------------------------------------------------------------------+

Formatted dataset:

data have_formatted;
   set have nobs=n;
   if _N_=1 then do;
      str = "[" || strip(str) || ",";
   end;
   else do;
      if _N_ < n then do;
         str = strip(str) || ",";
      end;
      else if _N_ = n then do;
         str = strip(str) || "]";
      end;
   end;
run;

+===================================================================+
|                                str                                |
+===================================================================+
| [{"method":"Get","parameters":{"int":1,"string":"teststring1"}},  |
+-------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":2,"string":"teststring2"}},   |
+-------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":3,"string":"teststring3"}},   |
+-------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":4,"string":"teststring4"}},   |
+-------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":5,"string":"teststring5"}},   |
+-------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":6,"string":"teststring6"}},   |
+-------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":7,"string":"teststring7"}},   |
+-------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":8,"string":"teststring8"}},   |
+-------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":9,"string":"teststring9"}},   |
+-------------------------------------------------------------------+
| {"method":"Get","parameters":{"int":10,"string":"teststring10"}}] |
+-------------------------------------------------------------------+

Parse using libname json:

filename res temp;

data save;
   file res;
   set have_formatted;
   put str;
run;

libname test json fileref=res;
filename res clear;

Result:

+---+------------+--------+---+--------------+
| P |     P1     |   P2   | V |    Value     |
+---+------------+--------+---+--------------+
| 1 | method     |        | 1 | Get          |
| 1 | parameters |        | 0 |              |
| 2 | parameters | int    | 1 | 1            |
| 2 | parameters | string | 1 | teststring1  |
| 1 | method     |        | 1 | Get          |
| 1 | parameters |        | 0 |              |
| 2 | parameters | int    | 1 | 2            |
| 2 | parameters | string | 1 | teststring2  |
| 1 | method     |        | 1 | Get          |
| 1 | parameters |        | 0 |              |
| 2 | parameters | int    | 1 | 3            |
| 2 | parameters | string | 1 | teststring3  |
| 1 | method     |        | 1 | Get          |
| 1 | parameters |        | 0 |              |
| . | ...        | ...    | . | ...          |
| 2 | parameters | string | 1 | teststring10 |
+---+------------+--------+---+--------------+