topic Re: JSON map with forward slash in values in SAS Programming

JSON map with forward slash in values

John2020 — Wed, 12 Aug 2020 15:45:44 GMT

I have JSON where the values have a forward slash in them such as "Amt w/total"

{
   "data" : [
     { "a" : 1 , "Amt w/total" : 10 },
    ]
}

So when the map is created it looks like

{
    "NAME": "Amt_w_total",
    "TYPE": "NUMERIC",
    "PATH": "/root/data/Amt w/total"
}

This reads in as all missing because I assume it's treating the / as a separator in the path and looking for values of "Amt w" and "total".

Is there any way to tell it the slash is part of the value?

Re: JSON map with forward slash in values

ChrisNZ — Thu, 13 Aug 2020 02:18:49 GMT

Just a long shot:

Maybe SAS expects the / to be escaped, as in

 Amt w\/total

and gets confused?

Does the map work if you correct the map?

Re: JSON map with forward slash in values

John2020 — Thu, 13 Aug 2020 13:55:31 GMT

Doesn't seem to. I've tried backlashes, doubling up the slashes, and adding single quotes. If the values can be escaped, I haven't figured out the magic character yet.

Re: JSON map with forward slash in values

Tom — Thu, 13 Aug 2020 14:12:21 GMT

Ask SAS support for help.

It is definitely valid to have / in key value.

https://www.w3schools.com/js/js_json_objects.asp#:~:text=Keys%20must%20be%20strings%2C%20and,is%20separated%20by%20a%20comma.

You can try it yourself:

https://www.w3schools.com/js/tryit.asp?filename=tryjson_object_loop

Not sure where to look for what is valid in that path syntax that the map files are using.

Re: JSON map with forward slash in values

Tom — Thu, 13 Aug 2020 14:29:01 GMT

If you cannot get it to work then perhaps your can made a modified version of the data that changes that value. Perhaps using unix tool like sed or even just reading the file in a datastep and using TRANWRD().

filename json temp;
options parmcards=json;
parcards4;
{
   "data" : [
     { "a" : 1 , "Amt w/total" : 10 }
    ]
}
;;;;

filename fixed temp;
data _null_;
  infile json;
  file fixed;
  input;
  _infile_=tranwrd(_infile_,'"Amt w/total"','"Amt w_total"');
run;

filename map temp;
libname json json map=map automap=reuse;

proc print data=json.data; 
run;

       ordinal_    ordinal_         Amt_w_
Obs      root        data      a     total

 1         1           1       1      10