Edit an existing JSON file

[valengvz]

Hi there, I'm working with a JSON file that looks like this:

{
   "parameters":{
      "type":"SEARCH_INDEX_LOADER",
      "filters":[
                        {
                                "type": "incrementalLoad",
                                "startTime": "2021-07-13T10:00:00.000Z"
                        }
      ]
   },
   "tasks":[
      {
         "parameters":{
            "type":"LOAD_DOCUMENT",
            "name":"EMPLEADO"
         }
      },      
      {
         "parameters":{
            "type":"LOAD_DOCUMENT",
            "name":"COMERCIO"
         }
      }

   ]
}

I'm looking for a way to change the "startTime": "2021-07-13T10:00:00.000Z", with the current date time, the only way that might work is to create the JSON file from scratch with PROC JSON procedure.

Does someone know if there is a way to do that inside SAS?

(I'm trying to do a reincremental reindexing for SAS visual investigator)

[jimbarbour]

Well, this isn't using any of SAS's JSON functions or procedures, but the quick and dirty way to do it is with a Data step and simply swap out the value, 1 for 1.  In the below code, I'm reading from and writing to a SAS dataset for ease of demonstration.  In reality, you'd want to read from and write to a text file.  Note that in the results that the original date-time, 13 July..., has been replaced with the current date-time of my SAS server, 23 July...

The Putlog statements are just there so it's easier to see what the code is doing; they're not essential to the final product of the program.

Code:

DATA	Have;
	INFILE	DATALINES	TRUNCOVER;
	INPUT
		Line_of_Data	$CHAR32767.;
DATALINES;
{
   "parameters":{
      "type":"SEARCH_INDEX_LOADER",
      "filters":[
                        {
                                "type": "incrementalLoad",
                                "startTime": "2021-07-13T10:00:00.000Z"
                        }
      ]
   },
   "tasks":[
      {
         "parameters":{
            "type":"LOAD_DOCUMENT",
            "name":"EMPLEADO"
         }
      },      
      {
         "parameters":{
            "type":"LOAD_DOCUMENT",
            "name":"COMERCIO"
         }
      }

   ]
}
;
RUN;

DATA	Want;
	KEEP	Line_of_Data;
	LENGTH	Time_Stamp	$128.;
	SET	Have;
	IF	INDEX(UPCASE(Line_of_Data), 'STARTTIME')	THEN
		DO;
			Start_Position	=	INDEX(UPCASE(Line_of_Data), 'STARTTIME') - 1;
			Time_Stamp		=	SCAN(Line_of_Data, 2, ' ');
			Stamp_Length	=	LENGTH(CAT('"startTime": ', Time_Stamp));
			PUTLOG	"NOTE:  Before "	Time_Stamp=;
			SAS_Time_Stamp	=	INPUT(COMPRESS(Time_Stamp,'"Zz'), ANYDTDTM20.);
			PUTLOG	"NOTE-  Before "	SAS_Time_Stamp=  SAS_Time_Stamp=	E8601DT23.3;
			PUTLOG	"NOTE-  ";
			SAS_Time_Stamp	=	DATETIME();
			PUTLOG	"NOTE-  After  "	SAS_Time_Stamp=  SAS_Time_Stamp=	E8601DT23.3;
			Time_Stamp		=	CATS('"', PUT(SAS_Time_Stamp, E8601DT23.3), 'Z"');
			PUTLOG	"NOTE-  After  "	Time_Stamp=;
			PUTLOG	"NOTE-  ";
			SUBSTR(Line_of_Data, Start_Position, Stamp_Length)	=	CAT('"startTime": ', Time_Stamp);
		END;
RUN;

Results:

Jim

[valengvz]

Thank you, I will start with this aproach.

[jimbarbour]

Sounds good.  Please let me know if you encounter any problems, and let me know how it goes.

Good luck,

Jim

[Tom · Accepted solution]

Easy to read the data from the file. Then you can use the power of the INPUT and PUT statements.

data _null_;
  infile have dlm=':' dsd truncover ;
  file want;
  input name :$100. @ ;
  if name = 'startTime' then do;
    now = intnx('dtday',datetime(),0,'b');
    col = max(1,verify(_infile_,' '));
    put @col '"startTime": "' now E8601DT23.3 'Z"';
  end;
  else put _infile_;
run;

Results:

{
   "parameters":{
      "type":"SEARCH_INDEX_LOADER",
      "filters":[
                        {
                                "type": "incrementalLoad",
                                "startTime": "2021-07-23T00:00:00.000Z"
                        }
      ]
   },
   "tasks":[
      {
         "parameters":{
            "type":"LOAD_DOCUMENT",
            "name":"EMPLEADO"
         }
      },
      {
         "parameters":{
            "type":"LOAD_DOCUMENT",
            "name":"COMERCIO"
         }
      }

   ]
}

[Vivi1997]

If I have my JSON in the following format:

{
   "parameters":{
      "type":"SEARCH_INDEX_LOADER",
      "filters":[
                        {
                                "type": "incrementalLoad",
                                "startTime": "2021-07-23T00:00:00.000Z"
                        }
      ]
   },
   "tasks":[
      {
         "parameters":{
            "type":"LOAD_DOCUMENT",
            "name":"EMPLEADO",
            "filters":[
                        {
                                "type": "incrementalLoad",
                                "startTime": "2021-07-23T00:00:00.000Z"
                        }
            ]
   },
   "tasks":[
      {
         "parameters":{
            "type":"LOAD_DOCUMENT",
            "name":"EMPLEADO"
         }
      },
      {
         "parameters":{
            "type":"LOAD_DOCUMENT",
            "name":"COMERCIO"
         }
      }

   ]
}

 And let say I want to edit the startTime under the second key i.e. tasks keyword instead of the first startTime under first parameters key. Then is it possible to do so?