在没有 SAS JSON libname 引擎的情况下读取 JSON 文件 [英] Reading JSON-file without the SAS JSON libname engine

问题描述

我正在尝试将数据从 JSON 文件获取到 SAS 中的表.不幸的是，我无法使用 SAS JSON 引擎.到目前为止，我已经将几乎所有数据都放到了一个表中.我只是缺少一些值.

I'm trying to getting data from a JSON-file to a table in SAS. Unfortunately I am not able to use the SAS JSON engine. So far I've got almost all the data to a table. I'm just missing a few values.

我的 JSON 文件如下所示:

My JSON file looks like this:

  {  
  "eventsLimited":false,
  "events":[  
    {  
      "_id":"1",
      "userId":"1",
      "timestamp":"2017-05-07T21:37:39.037Z",
      "detailedEvents":[  
        {  
          "eventType":"taskChanged",
          "taskId":"111",
          "changedProperties":[  
            {  
              "property":"totalSecondsSpent",
              "oldValue":0,
              "newValue":3600
            },
            {  
              "property":"totalSecondsEstimate",
              "oldValue":0,
              "newValue":144000
            }
          ]
        }
      ]
    },
    {  
      "_id":"2",
      "userId":"1",
      "timestamp":"2017-05-07T22:31:30.037Z",
      "detailedEvents":[  
        {  
          "eventType":"taskChanged",
          "taskId":"111",
          "changedProperties":[  
            {  
              "property":"totalSecondsSpent",
              "oldValue":3600,
              "newValue":5400
            }
          ]
        }
      ]
    }
  ]
}

我的 SAS 代码如下所示:

My SAS code looks like this:

data iHave;
    infile 'C:UsersMyUserDesktopMyJSONFile.txt' recfm=n dlm='{}[],';
    input value : $200. @@;

    if value in: ('"count"' '"calls"') then
        delete;
run;

libname iTest 'C:UsersMyUserDesktop';

data iTest.iTemp;
    set iHave;
    length name v $ 100;

    if value =: '"_id"' then
        n+1;
    name=scan(value,2,'"','m');
    v=scan(value,-2,'"','m');
    drop value;
run;

这会给我一个如下所示的输出:

This will give me an output that looks like this:

到目前为止一切顺利.对于数组中的每个对象，计数器 (n) 递增 1，并且键和值显示在两个不同的列中.

So far so good. For each object inside the array the counter (n) increments by one and the key and value is showed in a two different columns.

我的问题是我没有正确理解 changedProperties 数组中的内容.我的输出中未显示oldValue"键和newValue"键的两个值.例如，'v' 列中的第一个 newValue 应该是 3600.

My problem is that I don't get what's inside the changedProperties array right. The two values for the 'oldValue' key and 'newValue' key is not showed in my output. For example the first newValue should be 3600 in the 'v' column.

有谁知道我可以如何修复我的代码，这样我才能得到正确的输出?我认为问题在于括号中没有显示数字，在我的扫描中，我通过括号内的值进行搜索.

Does anybody know how i can fix my code so I will get my output right? I think the problem is that numbers is not shown in parentheses and in my scan i search by values inside of parentheses.

/克里斯

推荐答案

您的问题来自于您告诉 scan 函数单词由双引号 (")，使用 'm' 修饰符指定连续分隔符以及字符串开头和结尾处的分隔符并指示空字.

Your problem comes from the fact that you are telling the scan function that words are delimited by double quotes ("), with the 'm' modifier specifying that consecutive delimiters as well as delimiters at the start and end of the string are to be accounted for and indicate empty words.

所以，对于您的其他键/值对(即 "key":"value")，它可以工作，因为第一个 scan 会查找在第一个和第二个 " 之间，即 key 和第二个 scan 向后查找最后一个和前一个 "，即值；对于 oldValue 和 newValue 你的值没有被引用，这意味着两个 scan 函数找到相同的词，即键.

SO, while for your other key/value pairs (i.e., "key":"value"), it works because the first scan looks for the word that is between the first and second ", i.e. key and the second scan looks backwards for the word between the last and forelast ", i.e. value; for oldValue and newValue your value is not quoted which means that both scan functions find the same word, i.e the key.

您可以通过指示分隔符是冒号 (:) 并删除引号(如果有的话)来解决这个问题:

You could fix that by indicating that the delimiter is the colon (:) and remove the quotes if there are any:

name=dequote(scan(value,1,':','m'));
v=dequote(scan(value,2,':','m'));

这篇关于在没有 SAS JSON libname 引擎的情况下读取 JSON 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！