What an ugly format.
Anyway the issue with your example file is that it is over 1 Gigabyte of text. And since JSON engine stores everything into memory reading the whole file is taking too long. I was able to easily read the example file from that site.
Looking at your particular file they did try to format the JSON file consistently. So you might be able to break it into pieces and read it. I can this little program to find where the actual data records start and end.
24 data _null_;
125 infile json lrecl=50000 length=ll truncover;
126 input @;
127 line+1;
128 len=ll;
129 input @1 ch1 :$1. @ll ch2 $1. ;
130 if (ch1 ne '09'x) or (ch2 ne ',') then put line= ch1= ch2= len= ;
131 run;
NOTE: The infile JSON is:
Filename=C:\downloads\2023-12-01_UnitedHealthcare-Freedom-Insurance-Company_Insurer_PPO---NDC_PPO-NDC_in-netw
ork-rates.json,
RECFM=V,LRECL=50000,
File Size (bytes)=1216563805,
Last Modified=17Nov2023:08:32:41,
Create Time=06Dec2023:11:49:58
line=1 ch1= ch2= len=0
line=2 ch1={ ch2={ len=1
line=3 ch1=" ch2=, len=74
line=4 ch1=" ch2=, len=39
line=5 ch1=" ch2=, len=36
line=6 ch1=" ch2=, len=23
line=7 ch1=" ch2=, len=44181
line=8 ch1=" ch2=[ len=15
line=57364 ch1= ch2=} len=22002
line=57365 ch1= ch2= len=0
line=57366 ch1=] ch2=] len=5
line=57367 ch1=} ch2=} len=1
line=57368 ch1= ch2= len=8
NOTE: 57368 records were read from the infile JSON.
The minimum record length was 0.
The maximum record length was 44181.
NOTE: DATA statement used (Total process time):
real time 1.41 seconds
cpu time 1.21 seconds
So the first 8 lines are the basic data for that entity. Then all of the detailed records start on line 9 and end on line 57,364.
So if you make a copy of the file that includes lines 1 to 8 and lines 57364 to the end then the JSON engine should be able to read it.
I can read about 100 lines in 2 seconds.
%let st=%sysfunc(datetime());
filename subset temp;
data _null_;
infile json lrecl=50000;
file subset lrecl=50000;
input;
if _n_ in (1:8,9:108,57364:57368) then put _infile_;
run;
libname subset json ;
proc copy inlib=subset out=work;
run;
%put Elapsed time = %sysfunc(putn(%sysfunc(datetime())-&st,time12.4));
But to read about 1000 records takes over 42 seconds.
So reading 10 times more records takes 20 times as much time. That is why trying to read the whole file at once takes so long (or even does not finish).
So you might need to break it into a LOT of smaller files to be able to read it.
Click image to register for webinar
