BookmarkSubscribeRSS Feed
melligeri
Calcite | Level 5

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.

2 REPLIES 2
BillM_SAS
SAS Employee

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 
ChrisHemedinger
Community Manager

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  
SAS For Dummies 3rd Edition! Check out the new edition, covering SAS 9.4, SAS Viya, and all of the modern ways to use SAS!

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1050 views
  • 1 like
  • 3 in conversation