Importing a single-line json file in SAS

Question

I have data in the following json format:

{"metadata1":"val1","metadata2":"val2","data_rows":[{"var1":1,"var2":2,"var3":3},{"var1":4,"var2":5,"var3":6}]}

There are some metadata variables at the start, which only appear once, followed by multiple data records, all on the same line. How can I import this into a SAS dataset?

Answer 1

/*Create json file containing sample data*/
filename json "%sysfunc(pathname(work))\json.txt";
data _null_;
file json;
put '{"metadata1":"val1,","metadata2":"val2}","data_rows":[{"var1":1,"var2":2,"var3":3},{"var1":4,"var2":5,"var3":6}]}';
run;

/*Data step for importing the json file*/
data want;
infile json dsd dlm='},' lrecl = 1000000 n=1;
retain metadata1 metadata2;
if _n_ = 1 then input @'metadata1":' metadata1 :$8. @'metadata2":' metadata2 :$8. @;
input @'var1":' var1 :8. @'var2":' var2 :8. @'var3":' var3 :8. @@;
run;

Notes:

• The point for SAS to start reading each variable is set using @'string' logic.
• Setting , and } as delimiters and using : format modifiers on the input statement tells SAS to keep reading characters from the specified start point until it's read the maximum requested number or a delimiter has been reached.
• Setting dsd on the infile statement removes the double quotes from character data values and prevents any problems from occurring if character variables contain delimiters.
• The double trailing @ tells SAS to continue reading more records from the same line using the same logic until it reaches the end of the line.
• Metadata variables are handled as a special case using a separate input statement. They could easily be diverted to a single row in a separate file if desired.
• lrecl needs to be greater than or equal to the length of your file for this approach to work.
• Setting n=1 should help to reduce memory usage if your file is very large, by preventing SAS from attempting to buffer multiple input lines.