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?
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;
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;
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
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!
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.