JSON file parsing using Transparency in Coverage Payer files -

[Essen560500 · Accepted solution]

On the SAS server - so accessing it using 
filename myfile "/mnt/sasusers/&user./data/TiC/UHC_OHIO_PPO.json";

[Reeza]

FYI - this is my full code on SAS Studio (On Demand) and it ran out of memory but ran until then.

I would use the proc contents method and copy one data set over at a time. I don't think you actually need the alldata dataset, but the remainder will be joined to create that data set. It will at least allow you to debug the data.

filename target "/home/fkhurshed/Demo2/demo.json" encoding = 'utf-8' ;
filename fromzip ZIP "/home/fkhurshed/Demo2/2023-12-01_UnitedHealthcare-of-Ohio--Inc-_Insurer_PPO---NDC_PPO-NDC_in-network-rates.json.gz" GZIP;
 

data _null_;
    infile fromzip lrecl=256 recfm=F length=length eof=eof unbuf;
    file target lrecl=256 recfm=N;
    input;
    put _infile_  $varying256. length;
    return;
  eof:
    stop;
run;


libname target json;

/* Now you can access the data in the JSON file as SAS data sets */
proc copy in=target out=work;
run;

libname target;

[SASJedi]

Excellent suggestion about not needing the ALLDATA table, @Reeza!  It's huge in this file. All it takes is a minor modification to the PROC COPY step to exclude it:

/* Now you can access the data in the JSON file as SAS data sets */
proc copy in=target out=work;
   EXCLUDE ALLDATA; 
run;

[Tom]

That is too late in the process.

It is getting hung at the point when it processes the LIBNAME statement.  That is when it loads the JSON data into (what?).

[SASJedi]

When I ran the code in Enterprise Guide on my laptop, it did take significantly longer than running it in PC SAS. But for me, only a couple of minutes - not half an hour! 

[Essen560500]

Just to make sure I am tacking with you. Did it parse the data into various columns as well when you ran your code ? When I ran the following code noted below. I get the results in less than a min but the file is not parsed. I get 1 column by by 57368 rows. filename myfile "/mnt/sasusers/&user./data/TiC/UHC_OHIO_PPO.json";

data mydata;
infile myfile lrecl=32767;
input;
json = _infile_;
run;

[SASJedi]

Yes, I'm getting all of the JSON tables as SAS data sets. For example:

[Essen560500]

one last question on this 🙂 so if I am using SAS Studio and using the following to read in my file then what would be the most efficient way to get the dataset in the above format ? thanks again for all your help on this.
filename myfile "/mnt/sasusers/&user./data/TiC/UHC_OHIO_PPO.json";

[Tom]

SAS/Studio connected to what server?

What version of SAS?  I had trouble with SAS 9.4m5 but not with SAS 9.4m7. Perhaps SAS has made the JSON engine more efficient in new releases?

This program works on the SAS On Demand for Academics server.

filename url url
 'https://uhc-tic-mrf.azureedge.net/public-mrf/2023-12-01/2023-12-01_UnitedHealthcare-of-Ohio--Inc-_Insurer_PPO---NDC_PPO-NDC_in-network-rates.json.gz'
;
filename copy "%sysfunc(pathname(work))/copy.gz";
data _null_;
  infile url recfm=n; file copy recfm=n;
  input; put _infile_;
run;
filename gzip zip "%sysfunc(pathname(work))/copy.gz" gzip;
libname gzip json noalldata ordinalcount=all;
options compress=yes;
proc copy inlib=gzip outlib=work;
run;
libname gzip clear;
proc contents varnum data=work._all_; run;

[Reeza]

The NOALLDATA is a good option to know on the libname statement.

[Reeza]

You're possibly aware, but the JSON will be converted more into a relational database format, not a single flat file with all fields in one table. You'll need to join the tables to get the data you want most likely.

[Essen560500]

Thank you !!

[Tom]

Using a modern version of SAS helps.

I was able to read that file with SAS 9.4m7 on the same Windows machine that could not handle the file with SAS 9.4m5.

[Essen560500]

Mine is - Release: 3.6 (Basic Edition)

Supported browsers:
Microsoft Internet Explorer 11
Mozilla Firefox 21+
Google Chrome 27+
Apple Safari 6.0+ (on Apple OS X)
Build date: Jun 4, 2018 5:02:11 PM
SAS Mid-tier release: Jun 4, 2018 4:28:00 PM
Java Version: 1.7.0_111
SAS release: 9.04.01M4P11092016
SAS platform: Linux LIN X64 3.10.0-1160.105.1.el7.x86_64
Site name: ABBVIE INC-GPRD AMAZON CLOUD

[Tom]

You have to pay SAS annually to use their software. 

You should be using the most recent version, not something from 2016.

There is no reason to be running a 7 year old version of SAS. 

https://blogs.sas.com/content/iml/2013/08/02/how-old-is-your-version-of-sas-release-dates-for-sas-so...

---

@Essen560500 wrote:
Mine is - Release: 3.6 (Basic Edition)

Supported browsers:
Microsoft Internet Explorer 11
Mozilla Firefox 21+
Google Chrome 27+
Apple Safari 6.0+ (on Apple OS X)
Build date: Jun 4, 2018 5:02:11 PM
SAS Mid-tier release: Jun 4, 2018 4:28:00 PM
Java Version: 1.7.0_111
SAS release: 9.04.01M4P11092016
SAS platform: Linux LIN X64 3.10.0-1160.105.1.el7.x86_64
Site name: ABBVIE INC-GPRD AMAZON CLOUD

---