在 SAS 字段中解析 JSON

Question

SAS是否有一种简单的方法来解析SAS列中的JSON数据？ 我知道它可以在proc ds2中使用 json 对象来完成，但这是一种糟糕的方法。 我正在寻找一些让它变得微不足道的东西，例如 JSON libname 选项，但不必先将所有内容转储到临时文件中。

Answer 1

libname语句中有 json 引擎(阅读此处(。 但首先，您需要更改输入数据集，条件是它是一个 json：

有数据集：

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"}} |
+------------------------------------------------------------------+

格式化数据集：

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"}}] |
+-------------------------------------------------------------------+

使用 libname json 解析：

filename res temp;
data save;
file res;
set have_formatted;
put str;
run;
libname test json fileref=res;
filename res clear;

结果：

+---+------------+--------+---+--------------+
| 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 |
+---+------------+--------+---+--------------+