topic Re: Reading data from JSON as charcater in SAS Programming

Reading data from JSON as charcater

BayzidurRahman — Thu, 27 Apr 2023 05:56:32 GMT

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

Re: Reading data from JSON as charcater

AMSAS — Thu, 27 Apr 2023 12:55:06 GMT

@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.

Re: Reading data from JSON as charcater

bayzid — Fri, 28 Apr 2023 06:34:58 GMT

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.

Re: Reading data from JSON as charcater

Tom — Fri, 28 Apr 2023 14:06:30 GMT

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.

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

Re: Reading data from JSON as charcater

bayzid — Fri, 28 Apr 2023 14:12:52 GMT

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

Re: Reading data from JSON as charcater

Tom — Fri, 28 Apr 2023 14:24:14 GMT

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.

Re: Reading data from JSON as charcater

bayzid — Fri, 28 Apr 2023 14:35:53 GMT

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 ;

Re: Reading data from JSON as charcater

Tom — Fri, 28 Apr 2023 14:41:13 GMT

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"
;

Re: Reading data from JSON as charcater

bayzid — Fri, 28 Apr 2023 14:51:19 GMT

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

Re: Reading data from JSON as charcater

Tom — Fri, 28 Apr 2023 15:09:44 GMT

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"
        }
      ]
    }
  ]
}

Re: Reading data from JSON as charcater

Quentin — Fri, 28 Apr 2023 15:14:51 GMT

@bayzid wrote:

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

The documentation has a good explanation, as well as an example of creating a map from a json file, and then editing it.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsglobal/n1jfdetszx99ban1rl4zll6tej7j.htm#p1bwa176ppg0ydn135d8wl01vdcm

The map has the metadata to define the SAS variables type, length, label, etc, in the same way as you would use an the informat statement, length statement, label statement, etc if you were reading it by hand.

An alternative approach would be to read the data and let some fields come in as numeric variables. Then in a separate step you could convert all the numeric fields to character (and enforce a standard length). But the map approach is cleaner. If someone was kind enough to give you a data dictionary, you could use that to build the map file.

Re: Reading data from JSON as charcater

bayzid — Fri, 28 Apr 2023 15:17:53 GMT

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

Re: Reading data from JSON as charcater

Tom — Fri, 28 Apr 2023 15:24:51 GMT

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 ;

Re: Reading data from JSON as charcater

bayzid — Fri, 28 Apr 2023 15:28:17 GMT

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;

Re: Reading data from JSON as charcater

Tom — Fri, 28 Apr 2023 15:32:21 GMT

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 ;

Re: Reading data from JSON as charcater

BayzidurRahman — Fri, 28 Apr 2023 15:39:14 GMT

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.

Re: Reading data from JSON as charcater

Tom — Fri, 28 Apr 2023 15:54:37 GMT

I posted the steps for doing that in my first answer to this question.

But do it manually first to make sure you know what works.

Re: Reading data from JSON as charcater

Tom — Fri, 28 Apr 2023 18:02:12 GMT

@BayzidurRahman wrote:
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.

For a worked example post a small example JSON file.

Re: Reading data from JSON as charcater

bayzid — Fri, 28 Apr 2023 20:42:38 GMT

I have 23 types JSON file with same structure but different variables. Attached 3 types of file.

Re: Reading data from JSON as charcater

Tom — Sat, 29 Apr 2023 20:31:20 GMT

So first have SAS make a MAP file for one of the file types.

%let filename=c:\downloads\json\AmtProduct_xx.json;
filename rawmap "c:\downloads\json\Amt_Product_rawmap.json";
libname json json "&filename" map=rawmap automap=create;

Then have SAS read the map itself as a JSON file. Merge the two tables back together to make it more useful.

libname rawmap json ;
data variables;
  merge rawmap.datasets rawmap.datasets_variables;
  by ordinal_datasets;
run;

Result so far:

Now if you want those other three variables to be CHARACTER you need to change the TYPE value from NUMERIC to CHARACTER. And if you want a map file that will also make the variables with SAME length you need to have a LENGTH variable instead of CURRENT_LENGTH variable.

This is where you will need to make actual decisions. Which variables should be NUMERIC and which should be CHARACTER? For the CHARACTER variables how long each one needs to be.

So let's do a quick a dirty approach and just set every variable to CHARACTER and set the length to 200 for every one. While we are at at let's rename the ordinal variable to ROW.

data variables;
  set variables;
  if type = 'ORDINAL' then name='ROW';
  else do;
    type='CHARACTER';
    LENGTH=200;
  end;
  drop current_length;
run;

Result

Now that we have the metadata we want the JSON engine to use when converting the JSON text into SAS dataset(s) we need to write that back out as a MAP file.

filename map "c:\downloads\json\Amt_Product_map.json";
data _null_;
  file map ;
  if _n_=1 then put '{"DATASETS":' / '[{' @ ;
  if eof then put '}]'/ '}' ;
  set variables end=eof ;
  by ordinal_datasets;
  if first.ordinal_datasets then do;
    if _n_ > 1 then put ',' @;
    put '"DSNAME":' dsname :$quote. ',"TABLEPATH":' tablepath :$quote.
     /  ' ,"VARIABLES":'
     /  '  [' @
    ;
  end;
  else put '  ,' @;
  put '{"NAME":' name :$quote. ',"TYPE":' type :$quote. ',"PATH":' path :$quote. @ ;
  if length then put ',"LENGTH":' length @;
  put '}' ;
  if last.ordinal_datasets then put '  ]' ;
run;

Here is what the new map file looks like:

{"DATASETS":
[{"DSNAME":"root" ,"TABLEPATH":"/root"
 ,"VARIABLES":
  [{"NAME":"ROW" ,"TYPE":"ORDINAL" ,"PATH":"/root" }
  ,{"NAME":"AmtProductId" ,"TYPE":"CHARACTER" ,"PATH":"/root/AmtProductId" ,"LENGTH":200 }
  ,{"NAME":"CTPP_Id" ,"TYPE":"CHARACTER" ,"PATH":"/root/CTPP_Id" ,"LENGTH":200 }
  ,{"NAME":"TPP_Id" ,"TYPE":"CHARACTER" ,"PATH":"/root/TPP_Id" ,"LENGTH":200 }
  ,{"NAME":"MPP_Id" ,"TYPE":"CHARACTER" ,"PATH":"/root/MPP_Id" ,"LENGTH":200 }
  ]
}]
}

Now let's use it to read the original JSON file and check how the variables are defined and how a few of the observations look.

libname json json "&filename" map=map;
proc contents data=json.root varnum;
run;
proc print data=json.root(obs=10);
run;

So you just need to repeat that process 23 times for the 23 different types of files you have. Make sure to set the lengths long enough for any values that might exist in the other files you have not check yet.