topic Re: Extracting multiple values having same path in json using json map in sas in SAS Programming

Extracting multiple values having same path in json using json map in sas

melligeri — Thu, 07 Apr 2022 11:06:08 GMT

JSON

{
  "totalCount": 2,
  "facets": {},
  "content": [
    [
      {
        "name": "customer_ID",
        "value": "1"
      },
      {
        "name": "customer_name",
        "value": "John"
      }
    ]
  ]
}

How to get the values under "name" using a json map.
i.e i need to get the out as "customer_ID" and "customer_name" using a json map.

Below is my json map.

{
  "DATASETS": [
    {
      "DSNAME": "customers",
      "TABLEPATH": "/root/content",
      "VARIABLES": [
        {
          "NAME": "name",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/name"
        },
        {
          "NAME": "name",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/name"
        },
        {
          "NAME": "value",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/value"
        },
        {
          "NAME": "value",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/value"
        }
        
      ]
    }
  ]
}

The output i am getting is only "customer_name".

Please help.

Thanks.

Re: Extracting multiple values having same path in json using json map in sas

BillM_SAS — Thu, 07 Apr 2022 13:01:12 GMT

I just cut out the duplication in the map file that you supplied.

Map file named ./sasuser/json.map

{
  "DATASETS": [
    {
      "DSNAME": "customers",
      "TABLEPATH": "/root/content",
      "VARIABLES": [
        {
          "NAME": "name",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/name"
        },
        {
          "NAME": "value",
          "TYPE": "CHARACTER",
          "PATH": "/root/content/value"
        }        
      ]
    }
  ]
}

Existing JSON file (named ./sasuser/json.sas):

{
  "totalCount": 2,
  "facets": {},
  "content": [
    [
      {
        "name": "customer_ID",
        "value": "1"
      },
      {
        "name": "customer_name",
        "value": "John"
      }
    ]
  ]
}

SAS code run:

filename mapRef './sasuser/json.map';
libname x json './sasuser/json.sas' map=mapRef;
proc print data=x.customers; run;

OUTPUT:

Obs  name           value 
1    customer_ID    1 
2    customer_name  John

Re: Extracting multiple values having same path in json using json map in sas

ChrisHemedinger — Thu, 07 Apr 2022 14:24:05 GMT

I answered this on StackOverflow (and Reddit! Hitting all of the channels!) where it seems that the original poster wants the "name" values to be proper SAS variables. Achievable using what @BillM_SAS suggests, plus a transpose step:

libname j json fileref=test; proc transpose data=j.content out=want; id name; var value; run;

Output:

customer_ customer_ Obs _NAME_ ID name 1 value 1 John