topic Re: Edit raw json file prior to using proc json in SAS Enterprise Guide

Edit raw json file prior to using proc json

Sean_OConnor — Tue, 05 Feb 2019 11:24:23 GMT

Folks,

I need to edit a raw json file prior to reading it into SAS for analysis.

Below is raw data I'm using and code I'm using which I would like to extend.

Raw data

{"time_stamp":1545314383,"empId":"4215-4-1","unregistered":false}
{"time_stamp":154534383,"empId":"4215-4-1","unregistered":false}
{"time_stamp":15454383,"empId":"425-4-1","unregistered":false}
{"time_stamp":15454383,"emId":"4915-1","unregistered":false}

Code

data _null_;
  infile json2 end=eof;
  file json3 ;
  input ;
  if _n_=1 then put '[' @; else put ',' @;
  put _infile_;
  if eof then put ']';
run;

How data is changed

[{"time_stamp":1545314383,"empId":"4215-4-1","unregistered":false}
,{"time_stamp":154534383,"empId":"4215-4-1","unregistered":false}
,{"time_stamp":15454383,"empId":"425-4-1","unregistered":false}
,{"time_stamp":15453383,"emId":"4915-1","unregistered":false}
]

I would like to extend the code to turn the time_stamp variable to character as I'm running into issues when it's read into SAS.

So I would like the following

{"time_stamp":"1545314383","empId":"4215-4-1","unregistered":false}
{"time_stamp":"154534383","empId":"4215-4-1","unregistered":false}
{"time_stamp":"15454383","empId":"425-4-1","unregistered":false}
{"time_stamp":"154531383","emId":"4915-1","unregistered":false}

Any help is appreciated.

Re: Edit raw json file prior to using proc json

BillM_SAS — Tue, 05 Feb 2019 14:52:38 GMT

You do not explain what problem you are having with the time_stamp field data when read into SAS. If you are using the JSON LIBNAME engine note that you can modify the resultant map file and assign a format to the time_stamp field. This may solve your problem.

Re: Edit raw json file prior to using proc json

TomKari — Tue, 05 Feb 2019 14:53:40 GMT

Not exhaustively tested:

data _null_;
	length InString $32767;
	retain _RX1;
	infile json2 end=eof;
	file json3;
	input;
	InString = _infile_;

	if _n_=1 then do;
		_RX1 = prxparse('s~\{"time_stamp":([[:digit:]]*),"empId":"~{"time_stamp":"$1","empId":"~');
		put '[' @;
	end;
	else put ',' @;
	call prxchange(_RX1, -1, InString);
	put InString;

	if eof then
		put ']';
run;

Tom

Re: Edit raw json file prior to using proc json

Sean_OConnor — Tue, 05 Feb 2019 14:56:43 GMT

SAS is reading it in in scientific notation

So the following values

1545317114383

Comes out as such

1.5453171E12

Re: Edit raw json file prior to using proc json

Tom — Tue, 05 Feb 2019 15:11:32 GMT

No need to change the JSON file. Just change the MAP file that says how to convert it.

If you let SAS generate the map then you will have a definition like this for time_stamp.

       {
          "NAME": "time_stamp",
          "TYPE": "NUMERIC",
          "PATH": "/root/time_stamp"
        },

Which you can just change to this to have it make it as a character string of length 20.

       {
          "NAME": "time_stamp",
          "TYPE": "CHARACTER", "LENGTH": 20,
          "PATH": "/root/time_stamp"
        },

Re: Edit raw json file prior to using proc json

Sean_OConnor — Tue, 05 Feb 2019 16:05:53 GMT

Hi Tom,

I'm just wondering is it possible to add labels at this stage too? So I would like to have labels for my variables in my stamp too?

Re: Edit raw json file prior to using proc json

Tom — Tue, 05 Feb 2019 16:08:43 GMT

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lestmtsglobal&docsetTarget=n1jfdetszx99ban1rl4zll6tej7j.htm&locale=en

name, type, length, format, informat, label

Re: Edit raw json file prior to using proc json

ballardw — Tue, 05 Feb 2019 16:20:48 GMT

@Sean_OConnor wrote:

SAS is reading it in in scientific notation

So the following values
1545317114383
Comes out as such
1.5453171E12

That is likely a byproduct of a generic import routine assigning a default format of BEST12. to the values.

Please see the below code:

data _null_;
   x=1545317114383;
   put "Best12. format" x= best12. "Best13. format" x= best13.;
run;

Note the different appearance of the value as shown in the log with different format.

You can change the format after the data is imported to a preferred appearance either manually or with Proc Datasets.