How to Import JSON into SAS without the LIBNAME Statement
For users running SAS 9.4 maintenance release 4 or later, the LIBNAME JSON engine is a great feature that allows JSON data to be read into SAS with only a few lines of code. For those without access to the JSON engine, this task takes a bit more effort. In this article, we will explore one method of converting JSON data to SAS datasets. This method is particularly useful if your JSON file contains multiple tables of varying structures, and could even be used on nested JSONs.
In a previous Butterfly blog, we looked at how to export SAS data into JSON files. I used this method to export SASHELP datasets on comets and flags into one JSON file. The JSON file follows a simple values/ array format as you can see below. Notice how these two datasets, while being contained within the same JSON file, actually have different structures.
In order to successfully import these two datasets, we will therefore need to write two separate DATA steps which ‘point at’ only the columns we want in the given table and ignore the columns which are not relevant. To achieve this, we will use the SAS INFILE statement.
The SAS INFILE statement allows text-based files to be read within the DATA step, making it an ideal candidate for importing JSON data into SAS. The picture below demonstrates how we would do this for the COMET dataset.
The INFILE statement is used to identify the location of the external file and apply relevant options. The options specified were chosen to suit a JSON file containing multiple tables of different structures. For more information on the available options, see the SAS documentation [https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/n1rill4udj0tfun1fvce3j401plo.htm].
The INPUT statement is responsible for parsing the data from the external file specified in the INFILE statement. @”pointer” specifies the starting position in the JSON file for reading data.
The variable-name tells SAS what to name the variable it reads this data into, with : informat specifying the desired informat.
@”variable-name” can be used as many times as necessary to read data from different positions in the JSON file (i.e. reading different variables). “@@” allows for reading multiple observations of each variable as the DATA step loops.
The DATA step will loop until all observations corresponding to the pointer/ variable-name combinations have been parsed.
The above example allowed us to read the COMET dataset into SAS. Another example is provided here for reading FLAGS dataset. Notice how the overall structure of the code is the same.
While this method may not be the most elegant, it successfully skirts legacy versions of SAS’ lack of support for reading JSON files. This allows users without access to the JSON libname engine to successfully implement JSON data into their SAS code.
