在 SAS 宏中解析 JSON 对象 - 第 2 部分 - 使用 OUTPUT 函数处理嵌套值 [英] Parse JSON object in SAS macro - Part 2 - using OUTPUT function to handle nested values

问题描述

这个问题与:之前的问题链接

我有一个如下所示的 JSON 文件:

<预><代码> [{"rxnorm_id": "999999999","drug_name": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",计划":[{"plan_id_type": "xxxxxxxxxxxxxxx","plan_id": "999999999999999","drug_tier": "xxxxxxxxxxxxxxx",prior_authorization":假，step_therapy":假，数量限制":假},

我可以使用以下代码将每一行都导入到具有 'rxnorm_id 和 drug_name 的 SAS 中:

 >文件名数据 url 'http://stg-oh-medicaid.molinahealthcare.com/JSON/Drugs_Molina_Healthcare.json'；数据公式；infile 数据 lrecl = 32000 trunco​​ver scanover；输入@'"rxnorm_id": "' rxnorm_id $255.@'"drug_name": "'drug_name $255.@'"plan_id_type": "' plan_id_type $255.@'"plan_id": "' plan_id $255.@'"drug_tier": "' drug_tier $255.@'"prior_authorization": 'prior_authorization $255.@'"step_therapy": ' step_therapy $255.@'"quantity_limit": 'quantity_limit $255.;rxnorm_id = scan(rxnorm_id,1,'",');Drug_name = scan(drug_name,1,'",');plan_id_type = scan(plan_id_type,1,'",');plan_id = scan(plan_id,1,'",');Drug_tier = scan(drug_tier,1,'",');先验授权 = 扫描(先验授权，1，'"，')；step_therapy = scan(step_therapy,1,'",');数量限制 = 扫描(数量限制，1，'"，')；跑步;

但是，我想获取位于 rxnorm 和药物名称值之间的计划"嵌套中的所有值.有人建议使用 SAS 中的 OUTPUT 选项来查看丢失的行.有人对我的代码有很好的修复吗?

谢谢

解决方案

从 9.4 开始，在 SAS 中解析 JSON 的最佳方法是 使用 PROC GROOVY.这就是我推荐的.您也可以使用 DS2.如果你喜欢冒险，并且在 9.4m3 上，你也可以使用 PROC LUA.这就是我要尝试的，因为它允许您轻松操作 SAS 数据集.

话虽如此，如果您可以依靠示例的简单结构，那么您可以仅选择具有字段的行，并在数据步骤中使用正则表达式以您想要的格式输出它们:

需要的数据；infile 'c:/tmp/json_snippet.txt';长度字段 $20 数据 $100；保留现场数据；保留再；输入;如果 _n_ = 1 则执行；re = prxparse('/"(.*?)": "?(true|false|.*?(?="))/');结尾;如果 prxmatch(re,_infile_);/* grep 仅匹配行 */调用 prxposn(re,1,start,len);field = substr(_infile_,start,len);调用 prxposn(re,2,start,len);数据 = substr(_infile_,start,len);跑步;

告诫空客:有位智者说过，当您使用正则表达式解决问题时，现在您有两个问题:).可能出错的事情包括:

• 换行
• 使用 ' 代替 " 作为字符串分隔符
• 长度
• 混合类型

This question is related to: prior question link

I have a JSON file that looks like:

    [
      {
        "rxnorm_id": "999999999",
        "drug_name": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
        "plans": [
          {
            "plan_id_type": "xxxxxxxxxxxxx",
            "plan_id": "999999999999999",
            "drug_tier": "xxxxxxxxxxxxxxx",
            "prior_authorization": false,
            "step_therapy": false,
            "quantity_limit": false
           },

I am able to import every line into SAS that has 'rxnorm_id and drug_name using this code:

    >
    filename data url 'http://stg-oh-medicaid.molinahealthcare.com/JSON/Drugs_Molina_Healthcare.json';
    data formularies;
    infile data lrecl = 32000 truncover scanover;
    input   @'"rxnorm_id": "' rxnorm_id $255.
    @'"drug_name": "' drug_name $255.
    @'"plan_id_type": "' plan_id_type $255. 
    @'"plan_id": "' plan_id $255.
    @'"drug_tier": "' drug_tier $255.
    @'"prior_authorization": ' prior_authorization $255.
    @'"step_therapy": ' step_therapy $255.
    @'"quantity_limit": ' quantity_limit $255.;
    rxnorm_id = scan(rxnorm_id,1,'",');
    drug_name = scan(drug_name,1,'",');
    plan_id_type = scan(plan_id_type,1,'",');
    plan_id = scan(plan_id,1,'",');
    drug_tier = scan(drug_tier,1,'",');
    prior_authorization = scan(prior_authorization,1,'",');
    step_therapy = scan(step_therapy,1,'",');
    quantity_limit = scan(quantity_limit,1,'",');
    run;

But, I want to pick up all of the values in the 'plans' nest that are in between the rxnorm and drug name values. Someone suggested using the OUTPUT option in SAS to see the missing rows. Anyone got a good fix to my code to do this?

Thanks

解决方案

As of 9.4, the best way to parse JSON in SAS is using PROC GROOVY. That is what I recommend. You can also do it with DS2. If you are adventurous, and on 9.4m3, you can also use PROC LUA. That is what I would try, since it allows you to manipulate SAS datasets easily.

That being said, if you can rely on the simple structure of your example, then you can select only the lines that have fields and output them in the format you wanted using regular expressions in data step:

data want;
    infile 'c:/tmp/json_snippet.txt';
    length field $20 data $100;
    keep field data;
    retain re;

    input;
    if _n_ = 1 then do;
        re = prxparse('/"(.*?)": "?(true|false|.*?(?="))/');
    end;

    if prxmatch(re,_infile_); /* grep only matching lines */

    call prxposn(re,1,start,len);
    field = substr(_infile_,start,len);
    call prxposn(re,2,start,len);
    data  = substr(_infile_,start,len);
run;

Caveat emptor: A wise person said that when you solve a problem using regular expressions, now you have two problems :). Among the things that can go wrong:

• line breaks
• using ' instead of " for string delimiters
• lengths
• mixed types

这篇关于在 SAS 宏中解析 JSON 对象 - 第 2 部分 - 使用 OUTPUT 函数处理嵌套值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！