在 SAS 宏中解析 JSON 对象 [英] Parse JSON object in SAS macro

问题描述

这是输入的 JSON 文件.它必须在 SAS 数据集中进行解析.

Here is the input JSON file. It have to parse in SAS dataset.

"results":
[
 {
    "acct_nbr": 1234,
    "firstName": "John",
    "lastName": "Smith",
    "age": 25,
    "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
        }
 }
,
{
    "acct_nbr": 3456,
    "firstName": "Sam",
    "lastName": "Jones",
    "age": 32,
    "address": {
        "streetAddress": "25 2nd Street",
        "city": "New Jersy",
        "state": "NJ",
        "postalCode": "10081"
        }
 }
]

我想要 SAS 数据集中的地址字段的输出，如下所示:

And I want the output for only Address field in SAS dataset like this :

ACCT_NBR    FIELD_NAME  FIELD_VALUE
1234    streetAddress   21 2nd Street
1234    city    New York
1234    state   NY
1234    postalCode  10021
3456    streetAddress   25 2nd Street
3456    city    New Jersy
3456    state   NJ
3456    postalCode  10081

我尝试了不同的方式，但没有类似的输出.甚至尝试从 PDF 进行扫描...但无法获得所需的输出...

I have tried separate way, but no similar output. even tried scanover from PDF ... but cannot get desired output...

这是我的代码......和输出......

here is my code......and output....

LIBNAME src  '/home/user/read_JSON';

filename data '/home/user/read_JSON/test2.json';
data src.testdata2;
    infile data lrecl = 32000 truncover scanover;
        input @'"streetAddress": "' streetAddress $255. @'"city": "' city $255. @'"state": "' state $2. @'"postalCode": "' postalCode $255.;
        streetAddress = substr(streetAddress,1,index(streetAddress,'",')-2);
        city = substr( city,1,index( city,'",')-2);
        state = substr(state,1,index(state,'",')-2);
        postalCode = substr(postalCode,1,index(postalCode,'",')-2);
run;

proc print data=src.testdata2;
RUN;

我的输出在 .lst 文件

The SAS System   09:44 Tuesday, January 14, 2014   1
           street                            postal
 Obs      Address         city      state     Code

  1     21 2nd Stree    New Yor       NY      10021"
  2     25 2nd Stree    New Jers      NJ      10081"

推荐答案

要使用纯 SAS 解决方案回答您的问题，您的问题是双重的:

To answer your question with a SAS-only solution, your problems are twofold:

• 使用 SCAN 而不是 substr 来获取非逗号/引号部分
• acct_nbr 是一个数字，因此您需要从输入中删除最后的引号.

• Use SCAN instead of substr to get the un-comma/quotationed portion
• acct_nbr is a number, so you need to remove the final quotation mark from the input.

这是正确的代码(我改变了目录，你需要把它们改回来):

Here's the correct code (I changed directories, you'll need to change them back):

filename data 'c:	empjson.txt';
data testdata2;
    infile data lrecl = 32000 truncover scanover;
        input 
            @'"acct_nbr": ' acct_nbr $255.
            @'"streetAddress": "' streetAddress $255. 
            @'"city": "' city $255. 
            @'"state": "' state $2. 
            @'"postalCode": "' postalCode $255.;

        acct_nbr=scan(acct_nbr,1,',"');
        streetAddress = scan(streetAddress,1,',"');
        city = scan(city,1,',"');
        state = scan(state,1,',"');
        postalCode = scan(postalCode,1,',"');
run;

proc print data=testdata2;
RUN;

这篇关于在 SAS 宏中解析 JSON 对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！