topic Re: Import JSON schema into an empty SAS dataset in SAS Programming

Import JSON schema into an empty SAS dataset

Haris — Mon, 21 Mar 2022 20:11:40 GMT

Need help to import a JSON schema into an empty SAS dataset. I have the schema file but no data yet. Tried this syntax but get an ERROR: Invalid Physical Name, ERROR: in the LIBNAMD statement.

filename map "C:\JSON_Schema.json";
libname JSONlib json map=map;

Direct read of the JSON schema as a data file creates a separate dataset for each field in the schema:

libname JSONdata json 'c:\JSON_Schema.json';

Is getting a preview of the eventual SAS dataset file structure even possible?

Re: Import JSON schema into an empty SAS dataset

Tom — Mon, 21 Mar 2022 23:21:01 GMT

You need to give the libname engine two text files. One with the actual JSON text to read and one with the MAP file that tells the JSON libref engine how to convert the JSON into SAS dataset.

Usually I just use the same name for both the libref and the filename that points to the actual JSON text. You then need a second fileref that points to either an existing MAP file or the location you want SAS to autogenerate one.

So your SAS code might look like this:

filename json 'some file with actual json text';
filename map 'some file with the mapping';
libname json json fileref=json map=map;

If you want to convert the file named "C:\JSON_Schema.json" to a SAS dataset then treat as the actual JSON text and not the MAP.

Re: Import JSON schema into an empty SAS dataset

Haris — Mon, 21 Mar 2022 23:34:21 GMT

Thanks for your input, TOM.

The thing is I only have the SCHEMA (I assume it's the same thing as the MAP but is it?) but no actual data to convert. Here is the first variable -- the unique identifier:

{  "$schema" : "http://json-schema.org/draft-04/schema#",
   "type" : "array",
	"items" : {
		"additionalProperties" : false,
		"properties" : {
			"dxout_identifier": {
				"type" : "string",
				"maxLength" : 255
			},
...

I received the schema ahead of the arrival of the actual data so that I could examine the file structure and prepare the code that will stack this new version on top of an older version of the same dataset with some new variables and some deletions and some format changes.

Re: Import JSON schema into an empty SAS dataset

Tom — Tue, 22 Mar 2022 00:29:38 GMT

Does not look like the format of the MAP files that JSON libname engine uses.

You could try to convert that into a definition of a dataset and/or all the way into a MAP so that you are ready when the real file comes. You should ask for an example with a few dummy records you can use to test it.

You could try converting by hand. Or if it is really large perhaps you could convert that JSON into some SAS datasets and then use a program to use that write a MAP file for SAS. Or perhaps just regular SAS code to define the dataset(s) you will need to create from the JSON text.

For example it looks like there is a variable named DXOUT_IDENTIFIER that should be defined as length $255.