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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.