topic Help reading a CSV file with a JSON field that has multi-level data in SAS Programming

Help reading a CSV file with a JSON field that has multi-level data

Areksoo — Thu, 07 Mar 2019 20:05:40 GMT

A few months ago, I asked for help reading a CSV with a JSON field and you guys came up with a solution that worked perfectly... now I got an even bigger problem. The fields in the JSON are more complex:

Cat_ID, JSON

1234,{"Name":"Buttons","Age":3,"Likes":[{"Item":"Food","Name":"Tuna"},{"Item":"Food","Name":"Chicken"},{"Item":"Toy","Name":"Laser Pointer"}],"Dislikes":[{"Item":"Food","Name":"Lemons"},{"Item":"Cat","Name":"Mittens"}],"Color":"Orange"}

1121,{"Name":"Mittens","Age":5,"Dislikes":[{"Item":"Cat","Name":" Buttons"}],"Color":"Black"}

The first record has multiple sub fields in the Likes and Dislikes categories. The second record has just 1 Dislike. There could be 0 to many Likes and Dislikes. Also every field in each category uses the same field name. So I can't just brute force combine whatever is before a colon to whatever is after.

Ideally I want to end up with 3 tables:

Details that contain: Cat_ID, Name, Age and Color.

Likes that contain: Cat_ID, Item and Name

Dislikes that contain: Cat_ID, Item and Name

Is there a way to parse by commas, but using the square brackets are delimiters?

Re: Help reading a CSV file with a JSON field that has multi-level data

Tom — Thu, 07 Mar 2019 20:23:31 GMT

Best bet would be convert the file to a valid JSON file and then read it using the JSON libname engine.

Perhaps something like this:

filename have 'myfile.csv';
filename fix temp;
data _null_;
  infile have dlm=',' firstobs=2 end=eof truncover;
  file fix  ;
  input cat_id +1 line $10000. ;
  if _n_=1 then put '[' @; else put ',' @;
  put '{"Cat_id":' cat_id ',' line ;
  if eof then put ']';
run;

data _null_;
  infile fix;
  input;
  list;
run;
filename map temp;
libname json json fileref=fix map=map automap=reuse;
proc copy inlib=json outlib=work;
run;

Re: Help reading a CSV file with a JSON field that has multi-level data

Tom — Thu, 07 Mar 2019 20:27:17 GMT

       ordinal_
Obs      root      Cat_id     Name      Age    Color

 1         1        1234     Buttons     3     Orange
 2         2        1121     Mittens     5     Black


       ordinal_    ordinal_
Obs      root        Likes     Item    Name

 1         1           1       Food    Tuna
 2         1           2       Food    Chicken
 3         1           3       Toy     Laser Pointer


       ordinal_    ordinal_
Obs      root      Dislikes    Item      Name

 1         1           1       Food    Lemons
 2         1           2       Cat     Mittens
 3         2           3       Cat      Buttons

Re: Help reading a CSV file with a JSON field that has multi-level data

Areksoo — Thu, 07 Mar 2019 23:06:22 GMT

So I had to play around with my data file a bit as I was having issues like the JSON field getting double quoted, but once I got it to a usable state, your code worked perfectly!

Thanks!