CODEWITHSUNDEEP
sas
B.Moran
B.Moran

Reputation: 13

SAS libname json with arrays

We are having a problem with the libname json, basically our dataset carries over the wrong field on object hierarchies.

here is a simple program:

filename resp "dataset.json";
filename pmap "map.json";
run;

libname example JSON fileref=resp map=pmap;
 proc datasets lib=example;
run;

data objects;
 set example.objects;
run;

the json dataset "dataset.json" looks like this:

{
  "objects": [
    {
      "field": "wrong_answer"
    },
    {
      "objectHierarchy": [
        {
          "map_to_this_level": "demo2"
        },
        {
          "map_to_this_level": "demo1"
        },
        {
          "map_to_this_level": "demo"
        }
      ],
      "field": "right_answer"
    }
  ]
}

and the map "map.json" looks like this:

{
  "DATASETS": [
    {
      "DSNAME": "objects",
      "TABLEPATH": "/root/objects/objectHierarchy",
      "VARIABLES": [
        {
          "NAME": "map_to_this_level",
          "TYPE": "CHARACTER",
          "PATH": "/root/objects/objectHierarchy/map_to_this_level",
          "CURRENT_LENGTH": 10
        },{
          "NAME": "field1",
          "TYPE": "CHARACTER",
          "PATH": "/root/objects/field",
          "CURRENT_LENGTH": 12
        }
      ]
    }
  ]
}

the resulting dataset "example.objects" looks like this:

map_to_this_level      field
demo2                  wrong_answer
demo1
demo                   right_answer

My question is why does the wrong_answer value from the field on the first object with an empty objectHierarchy get mapped onto the row of data from the next object with actual values for it's objectHierarchy field?

the data should look like this:

map_to_this_level      field
demo2                  right_answer
demo1
demo                   right_answer

Upvotes: 1

Views: 821

Answers (1)

Richard
Richard

Reputation: 27508

I presume a human expectation of:

field1        map_to_this_level
------------  -----------------
wrong_answer  <missing>
right_answer  demo2
right_answer  demo1
right_answer  demo

The JSON library engine is a serial decoder. The order of the properties in the json being parsed do not mate well with the map specified and the operation of the internal map interpreter (i.e. the SAS JSON library engine black box)

Consider this example with these small changes:

  • in json the field comes before objectHierarchy
  • in json wrong_answer has an empty array objectHierarchy. Note: if the objectHierarchy was not present, no row would be output for wrong_answer
  • in map the field1 value is retained using SAS JSON map feature DATASETS/VARIABLES/OPTIONS:["RETAIN"]
filename response catalog "work.json.sandbox.source";

data _null_;
  file response; input; put _infile_;
  datalines4;
{
  "objects": [
    {
      "field": "wrong_answer"
      ,
      "objectHierarchy": []
    },
    {
      "field": "right_answer"
      ,
      "objectHierarchy": [
        {
          "map_to_this_level": "demo2"
        },
        {
          "map_to_this_level": "demo1"
        },
        {
          "map_to_this_level": "demo"
        }
      ]
    }
  ]
}
;;;;
run;

filename pmap catalog "work.json.pmap.source";

data _null_;
  file pmap; input; put _infile_;
  datalines4;
{
  "DATASETS": [
    {
      "DSNAME": "objects",
      "TABLEPATH": "/root/objects/objectHierarchy",
      "VARIABLES": [
        {
          "NAME": "map_to_this_level",
          "TYPE": "CHARACTER",
          "PATH": "/root/objects/objectHierarchy/map_to_this_level",
          "CURRENT_LENGTH": 10
        },
        {
          "NAME": "field1",
          "TYPE": "CHARACTER",
          "PATH": "/root/objects/field",
          "CURRENT_LENGTH": 12

,  "OPTIONS": ["RETAIN"]

        }
      ]
    }
  ]
}
;;;;
run;

libname example JSON fileref=response map=pmap;

ods listing; options nocenter nodate nonumber formdlim='-'; title;

dm 'clear output';

proc datasets lib=example;
run;

proc print data=example.alldata;
run;

proc print data=example.objects;
run;

dm 'output';

Output

        map_to_
         this_
 Obs     level        field1

   1               wrong_answer
   2     demo2     right_answer
   3     demo1     right_answer
   4     demo      right_answer

If your json can not be trusted to be aligned with the mappings processed by SAS JSON library engine you will have to either:

  • work the the json provider, or
  • find an alternative interpreting mediary (Python, C#, etc) that can output modified json or an alternate interpreted form such as csv, that can be consumed by SAS.

Upvotes: 1

Related Questions