BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Areksoo
Calcite | Level 5

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?   

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

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;
Tom
Super User Tom
Super User
       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
Areksoo
Calcite | Level 5

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!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

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
  • 3 replies
  • 2782 views
  • 1 like
  • 2 in conversation