topic Re: Concatenating JSON objects in SAS Programming

Concatenating JSON objects

bayzid — Wed, 19 Apr 2023 20:19:35 GMT

I am trying to concatenate multiple JSON files and then converting the resulting file into SAS format. How can I include the source filename as a new variable in the concatenated file?

Re: Concatenating JSON objects

Quentin — Wed, 19 Apr 2023 20:44:43 GMT

Can you show sample code you are using to read and concatenate two JSON files? And perhaps attach two small sample JSON files (or include code to generate the two sample JSON files).

Re: Concatenating JSON objects

bayzid — Wed, 19 Apr 2023 20:48:35 GMT

data _null_;
/* Iterate over all the files input */
infile '\json_test\MyDrug*.json' end=eof;
/* Open the file to write the output to */
file '\MyDrug_all.json';

/* Read a line of input */
input;

/* If this is the first line in the file print it to the output file verbatim */
if _n_ = 1 then
put _infile_;
/* If this is the last line print it out verbatim */
else if eof then
put _infile_;
/* Otherwise we are in the middle somewhere */
else do
/* Check what the first character of the line is */
/* Its either [, ], or , */
first_character = substr(_infile_,1,1);
select (first_character);
/* If its [ we must remove it */
when ("[") do;
line_without_first_character = substr(_infile_,2);
put line_without_first_character;
end;
/* If its ] we must swap it to a , to allow more objects in the array */
when ("]") put "," @@;
otherwise put _infile_;
end;
end;
run;

Re: Concatenating JSON objects

bayzid — Wed, 19 Apr 2023 20:50:21 GMT

Re: Concatenating JSON objects

ballardw — Wed, 19 Apr 2023 20:52:42 GMT

I have no idea how you would do that in JSON objects. However if your read the JSON into separate data sets SAS can add the source data set name very easily. If you want the JSON file name that would mean you want to do something when creating the SAS data set from JSON to include the file, which may depend on how you are reading it.

If you have two (or more) SAS data sets the example code would look like:

data want;
    set data1 data2 indsname=fname;
    source = fname;
run;

The INDSNAME= option assigns the name of the data set contributing the observation to a temporary variable named after the =, in this case Fname. Temporary means that it is not written to the data set. To have the value kept you assign it to another variable that will be in the output.

Re: Concatenating JSON objects

Quentin — Wed, 19 Apr 2023 21:00:00 GMT

Looks like you're manually reading the json files and writing a new json file, right?

There is a filename= option file the INFILE statement which will create a temporary variable that has the name of the current input file. So it might be possible to use that to add a field to your output json file with the name of the file each record came from.

But seems like this would be easier to start by reading in each JSON file (with JSON engine), and then concatenate them in SAS. Or perhaps make a single filreref which points to all the JSON files, and read that using the JSON engine.

Re: Concatenating JSON objects

bayzid — Wed, 19 Apr 2023 21:00:31 GMT

Hi ballardw,

I know that it is very easy to add source file name while concatenating multiple SAS files. But for my situation, i need to concatenate the JSON files before converting to SAS file.

Re: Concatenating JSON objects

bayzid — Wed, 19 Apr 2023 21:07:10 GMT

Hi Quentin,

Can you please show me an example code?

Re: Concatenating JSON objects

Reeza — Wed, 19 Apr 2023 21:13:10 GMT

@bayzid wrote:

Hi ballardw,

I know that it is very easy to add source file name while concatenating multiple SAS files. But for my situation, i need to concatenate the JSON files before converting to SAS file.

I think this is where we disagree with you. You should read each file separately and append the results into one SAS file with the dates. Can you clarify why you think you need to combine them first?

Re: Concatenating JSON objects

bayzid — Wed, 19 Apr 2023 21:25:26 GMT

Hi Reeza,
For most of the variables, the length and variable type varies across datasets. SAS cannot handle this during concatenation.

Re: Concatenating JSON objects

Quentin — Wed, 19 Apr 2023 21:25:35 GMT

I would use a macro to read one file at a time and capture the name of the file, like:

%macro readjson(file=/*json file*/,out=/*output sas dataset*/) ;

filename jfile "&file" ;
libname jfile json;

data &out ;
  set jfile.root ;
  length sourcefilename $200 ;
  retain sourcefilename "%scan(&file,-1,/\)" ;
run ;

filename jfile clear ;
libname jfile clear ;

%mend readjson ;

%readjson(file=C:\...\MyDrug_8c209d34-1b3c-48a5-ae3a-90763c4f4d2e.json,out=want1)
%readjson(file=C:\...\MyDrug_5b056720-1620-4e67-9f42-7a0587da9a08.json,out=want2)

data all ;
  set want1 want2 ;
run ;

One challenge with that approach is you end up with variable type conflicts (your column Schedule) and length conflicts. So with this approach, I use a data dictionary to impose types/lengths after reading the data. Or, probably better, you could specify a json map when you read the data. I suppose putting all of the data into one json file would avoid this problem, but it still feels messy. If you're going to need to do this a lot, I think I would put in the time to define a map for the json file, and then use that to read each file into a SAS dataset, and then combine them.

Re: Concatenating JSON objects

bayzid — Wed, 19 Apr 2023 21:39:44 GMT

Hi,
Thanks for the code. Unfortunately, it didn't work. Data want2 was not created and data want1 was empty.
I am trying to concatenate all the JSON file before conversation to SAS due to the reason you have mentioned.

Re: Concatenating JSON objects

Reeza — Wed, 19 Apr 2023 21:47:14 GMT

@bayzid wrote:

Hi Reeza,
For most of the variables, the length and variable type varies across datasets. SAS cannot handle this during concatenation.

That should have been your question. Fix your folder path accordingly and this will read all the files and combine them into one. Then use PROC TRANSPOSE to flip if you want the wide format.

filename dirtree url 
    'https://raw.githubusercontent.com/sasutils/macros/master/dirtree.sas';
%include dirtree /source2;
%dirtree(/home/fkhurshed/CLASS1    /* Pipe delimited directory list (default=.) */
        , out=filelist  /* Output dataset name */
        , maxdepth=1 /* Maximum tree depth */);

%macro read_json(myfile=);
    filename mydata &myfile.;
    libname myjson JSON fileref=mydata;

    proc datasets lib=myjson;
    quit;

    *how to parse the file has not been specified - looks like you need to specify the lengths;

    data file2save;
        length source $200. p1 $256. Value $256.;
        set myjson.alldata;
        source=&myfile.;
    run;

    proc append base=master data=file2save force;
    run;

    proc sql;
        drop table file2save;
    quit;

    filename mydata;
    libname myjson;
%mend read_json;

proc sql;
    drop table master;
quit;

data run_list;
    set filelist;
    where type='F';
    str=catt('%read_json(myfile="/home/fkhurshed/CLASS1/', filename, '");');
    call execute(str);
run;

Re: Concatenating JSON objects

Tom — Wed, 19 Apr 2023 22:10:58 GMT

@bayzid wrote:

Hi Reeza,
For most of the variables, the length and variable type varies across datasets. SAS cannot handle this during concatenation.

When you read the JSON files use the SAME mapping each time and the variables will be defined consistently.

Re: Concatenating JSON objects

bayzid — Wed, 19 Apr 2023 22:13:26 GMT

Hi Reeza,
Thanks for the code. It ran with lots of errors. The Master dataset was empty. The log file is attached.

Re: Concatenating JSON objects

Tom — Wed, 19 Apr 2023 22:16:30 GMT

Your JSON is sooooooo simple you could just read it as name/value pairs directly.

data tall ;
  length row col 8 name $32 value $256 ;
  length filen filename $256 ;
  infile "c:\downloads\MyDrug*.json" dlm='[{,:}]' truncover length=ll column=cc filename=filen;
  input @;
  if filen ne lag(filen) then row=0;
  filename=filen;
  row+1;
  do col=1 by 1 until(cc>ll);
    input name value @ ;
    name=dequote(name);
    if value='null' then value=' ';
    else value=dequote(value);
    if name ne ' ' then output;
  end;
run;

proc transpose data=tall out=wide(drop=_name_);
  by filename row ;
  id name;
  var value;
run;

proc print data=wide ;
 where row=1;
run;

Result

Re: Concatenating JSON objects

bayzid — Wed, 19 Apr 2023 22:36:10 GMT

Hi Tom,
That's excellent. I am almost there. With my complete data pool (>83k JSON files into 21 groups) the tall is being created without any issue but the transpose proc is not working. It's returning the following error message.
NOTE: The above message was for the following BY group:
filename=C:\...ts\JSON_1st_0203
ERROR: The ID value "null" occurs twice in the same BY group.
ERROR: The ID value "false" occurs twice in the same BY group.

Re: Concatenating JSON objects

Reeza — Wed, 19 Apr 2023 22:57:02 GMT

This is your issue from the log.
https://communities.sas.com/t5/SAS-Programming/JSON-File-is-locked-error/td-p/851162

Re: Concatenating JSON objects

bayzid — Thu, 20 Apr 2023 00:13:58 GMT

Hi Tom,
I found the reason for the error message. You are using comma as a delimiter but it also appears in some of the values. Then the problem happens.

Is there a way to get around this problem easily?

Re: Concatenating JSON objects

Tom — Thu, 20 Apr 2023 12:03:14 GMT

You could try adding the DSD option, that will allow the quotes to protect the delimiters.

data tall ;
  length row col 8 name $32 value $256 ;
  length filen filename $256 ;
  infile "c:\downloads\MyDrug*.json" dlm='[{,:}]' dsd truncover length=ll column=cc filename=filen;
  input @;
  if filen ne lag(filen) then row=0;
  filename=filen;
  row+1;
  do col=1 by 1 until(cc>ll);
    input name value @;
    name=dequote(name);
    if value='null' then value=' ';
    else value=dequote(value);
    if name ne ' ' then output;
  end;
run;

The [{ or ,{ at the start of each line will cause there to be two extra empty values, but the IF statement that ignores the pairs without a name will cause those to be ignored.