Reading data from JSON as charcater

[BayzidurRahman]

Hi,
Is there any simple option to read all data from JSON file as character using JSON libname engine?

[AMSAS]

@BayzidurRahman I suggest you check the LIBNAME Statement: JSON Engine documentation and the ALLDATA= "name" LIBNAME Option

ALLDATA= "name"

renames the ALLDATA data set to the specified name. A data set named ALLDATA is created by default when the JSON engine runs. The ALLDATA data set contains all of the information in the JSON file. This option creates the data set with the specified name instead. The new name must meet the conventions for SAS names.

[bayzid]

May be my question was not clear enough. I don't have any issue with the data name.

I want to import all the variables from a JASON file as character variables.

[Tom]

You need to be more specific about what you need.  A JSON file can have a very complex structure, so a general solution that works for ANY JSON file would be extremely complex.

To do the general solution I would recommend taking the following approach.

1. Use the JSON engine with AUTOMAP feature to have SAS make a GUESS at how to structure the resulting dataset(s).
2. Use the JSON engine to convert the MAP file generated (the MAP file is itself a JSON file).
3. Modify the datasets generated in step2 so that all of the variables are now character.
4. Use the modified dataset to create a new MAP file.
5. Use the modified MAP file to read the original JSON file.

[bayzid]

My JASON file has very simple structure as follows. A sample code would be highly appreciated.

[Tom]

If every line has the same four variables then just read it like it was a text file.

Remember to adjust for the goofy way JSON handles quotes.

Here is an example that assumes the strings are always NAME:VALUE and that they always have the same four variables in the same order.

data want;
  infile 'myfile.json' dsd dlm='{[,:]}' truncover ;
  row+1;
  input @;
  _infile_=tranwrd(_infile_,'\\','00'x);
  _infile_=tranwrd(_infile_,'\"','""');
  _infile_=tranwrd(_infile_,'00'x,'\');
  input (4*var1  2*var2 2*var3 2*var4) (:$200.);
run;

If that fails because it is not so regular then go back to the code from your other question that reads the lines into NAME/VALUE pairs.

[bayzid]

The problem with the other code of Name/Value pair is that some of the concatenated tables are very large (~1billion observations in wide format), although my machine has enough power, for such big files it is always losing some data.
The other alternative is to convert individual JSON files into SAS file where all the variables are character and then append those SAS files using data step with wildcard after file name.
I have been using a macro loop to convert the individual JSON files into SAS file using the following code. Now I am looking for some options there so that it converts all the variables as character.

/*Import the json files one by one from the directory and creating a dataset in the Work library*/ 
%macro importjson;                                                   
   %do i=1 %to &nobs;                                               
      libname test JSON "C:\Users\mq10004085\Macquarie University\MRFF Aged care Data - Documents\JSON_1st_02032023\&&file&i...json" ORDINALCOUNT= NONE ;*NOALLDATA; 
data sas.&&ds&i ; /*Creating tables in the Work library instead of the samt lib*/
set test.Root;
dataname="&&file&i"; /*Variable containing the json file names*/
Phid="&i"; /*Phid containing the index*/
run;                                                 
%end;                                                               
%mend importjson;                                                                                                                            
%importjson ;

[Tom]

Sorry to sound like broken record. 

Create a MAP file that reads the JSON files the way you want.

Then modify your macro to use that map file.

libname test JSON "C:\Users\mq10004085\Macquarie University\MRFF Aged care Data - Documents\JSON_1st_02032023\&&file&i...json" 
  ORDINALCOUNT= NONE 
  MAP="C:\Users\mq10004085\Macquarie University\myjson.map"
;

[bayzid]

Sorry for not understanding your point. This is because I have very limited knowledge about map file.

[Tom]

They are just text files.  You can edit them by hand if you want.

Make sure to switch form the CURRENT_LENGTH field that the automap generates to the more rigid LENGTH field.  Otherwise you will still get individual datasets with different length character variables.

Run this code to see an example of a MAP file.

filename json temp;
proc json out=json;
   export sashelp.class;
run;

filename map temp;
libname json json map=map automap=reuse;

data _null_;
  infile map;
  input;
  put _infile_;
run;

{
  "DATASETS": [
    {
      "DSNAME": "root",
      "TABLEPATH": "/root",
      "VARIABLES": [
        {
          "NAME": "ordinal_root",
          "TYPE": "ORDINAL",
          "PATH": "/root"
        },
        {
          "NAME": "SASJSONExport",
          "TYPE": "CHARACTER",
          "PATH": "/root/SASJSONExport",
          "CURRENT_LENGTH": 3
        }
      ]
    },
    {
      "DSNAME": "SASTableData_CLASS",
      "TABLEPATH": "/root/SASTableData+CLASS",
      "VARIABLES": [
        {
          "NAME": "ordinal_root",
          "TYPE": "ORDINAL",
          "PATH": "/root"
        },
        {
          "NAME": "ordinal_SASTableData_CLASS",
          "TYPE": "ORDINAL",
          "PATH": "/root/SASTableData+CLASS"
        },
        {
          "NAME": "Name",
          "TYPE": "CHARACTER",
          "PATH": "/root/SASTableData+CLASS/Name",
          "CURRENT_LENGTH": 7
        },
        {
          "NAME": "Sex",
          "TYPE": "CHARACTER",
          "PATH": "/root/SASTableData+CLASS/Sex",
          "CURRENT_LENGTH": 1
        },
        {
          "NAME": "Age",
          "TYPE": "NUMERIC",
          "PATH": "/root/SASTableData+CLASS/Age"
        },
        {
          "NAME": "Height",
          "TYPE": "NUMERIC",
          "PATH": "/root/SASTableData+CLASS/Height"
        },
        {
          "NAME": "Weight",
          "TYPE": "NUMERIC",
          "PATH": "/root/SASTableData+CLASS/Weight"
        }
      ]
    }
  ]
}

[bayzid]

Thanks.
A dumb question: in your code, where should I put the name of my JSON file?

[Tom]

No idea what code you are talking about.

You can either place the name of the JSON file in the LIBNAME statement.  Or you can use a FILENAME statement to make a fileref that points to the JSON file and then use the fileref in the LIBNAME statement.  If the fileref and the libref that the LIBNAME is defining are the same then you don't need to tell the LIBNAME statement what fileref to use, it will just look for one with that same name.

Example:

filename myfile 'xxx.json';
libname myfile json ;

[bayzid]

I was talking about the following.
filename json temp;
proc json out=json;
export sashelp.class;
run;

filename map temp;
libname json json map=map automap=reuse;

data _null_;
infile map;
input;
put _infile_;
run;

[Tom]

Replace the TEMP fileref engine with the actual name of the file.

So if you have a json file name myfile.json and you want to write the map file a file name myfile.map then you would run:

filename json 'myfile.json';
filename map 'myfile.map';
libname json json map=map automap=reuse;

Then you could look at myfile.map and make the desired changes and then try reading the JSON file using code like:

filename json 'myfile.json';
filename map 'myfile.map';
libname json json map=map ;

[BayzidurRahman]

Thanks. Got it.
Is there any way to change all the TYPE to Character and length to a fixed number by doing some code? This is because I have thousands of files and need to pass a macro through them.