Import a JSONL File?

mdavidson — Tue, 23 Jun 2020 13:53:31 GMT

I know the JSON libname is able to read in a .json file, but from the documentation I've read it does not support reading .jsonl (JSON lines) files. I've seen some solutions on how to export and create a JSONL file but I have not seen anything where SAS is able to read one in.

Has anyone come up with a way to read these in? I've attached an example .jsonl file for reference.

Re: Import a JSONL File?

AhmedAl_Attar — Tue, 23 Jun 2020 16:32:00 GMT

@mdavidson

How about converting the JSONL into a JSON, then using SAS to import it?

filename jsonl "some.jsonl";
filename json temp;
filename map temp;

data _null_;
	infile jsonl end=eof;
	file json;
	put '[{"records":['; /* Required for JSON formatting */
	do until (eof);
		input;
		line = STRIP(_infile_);
		if (eof=0) then
			line=cats(line, ',');

		put line;
	end;
	put ']}]'; /* Required for JSON formatting */
	stop;
run;
libname json json automap=create map=map;
proc print data=json.records; run;
proc print data=json.records_children; run;

Hope this helps,

Ahmed

Re: Import a JSONL File?

Ksharp — Wed, 24 Jun 2020 11:59:22 GMT

@ChrisHemedinger wrote a blog about it before.

Search JSONL at http://blogs.sas.com

Re: Import a JSONL File?

BhargavDesai — Fri, 08 Jan 2021 18:52:58 GMT

can't find JASONL blog on that site.

Re: Import a JSONL File?

BhargavDesai — Fri, 08 Jan 2021 18:56:35 GMT

Hello,

I used your code to read in jasonl file; the program created 3 datasets - Records, Record_quantity and Records_value. How can I join them?

Here is my sample data format.

{"Direction":"Export","Reporter":"United States","Partner":"Canada","Product":"7208102500","Currency":"USD","Value":{"Apr 2020":245093.0,"May 2020":129167.0,"Aug 2020":526384.0,"Jun 2020":149208.0,"Oct 2020":492946.0,"Sep 2020":469311.0,"Jul 2020":154286.0,"Feb 2020":683111.0,"Mar 2020":510026.0,"Jan 2020":140227.0},"Unit":"KG","Quantity":{"Apr 2020":281329.0,"May 2020":128224.0,"Aug 2020":708810.0,"Jun 2020":208647.0,"Oct 2020":628174.0,"Sep 2020":654864.0,"Jul 2020":224882.0,"Feb 2020":809587.0,"Mar 2020":613111.0,"Jan 2020":163688.0},"ReporterIso":"US","PartnerIso":"CA"}
{"Direction":"Export","Reporter":"United States","Partner":"United Kingdom","Product":"7208520000","Currency":"USD","Value":{"Apr 2020":13881.0,"May 2020":8396.0,"Oct 2020":2990.0,"Sep 2020":2602.0,"Aug 2020":9012.0,"Jan 2020":10011.0,"Jul 2020":3821.0,"Mar 2020":10734.0,"Feb 2020":18280.0},"Unit":"KG","Quantity":{"Apr 2020":2720.0,"May 2020":1811.0,"Oct 2020":631.0,"Sep 2020":191.0,"Aug 2020":3851.0,"Jan 2020":2359.0,"Jul 2020":1549.0,"Mar 2020":2538.0,"Feb 2020":8786.0},"ReporterIso":"US","PartnerIso":"GB"}
{"Direction":"Export","Reporter":"United States","Partner":"Thailand","Product":"7208900000","Currency":"USD","Value":{"Apr 2020":60640.0,"May 2020":256773.0,"Jun 2020":303892.0,"Jul 2020":89638.0,"Jan 2020":260132.0},"Unit":"KG","Quantity":{"Apr 2020":1034.0,"May 2020":7665.0,"Jun 2020":6757.0,"Jul 2020":2428.0,"Jan 2020":7346.0},"ReporterIso":"US","PartnerIso":"TH"}
{"Direction":"Export","Reporter":"United States","Partner":"Malaysia","Product":"7206100000","Currency":"USD","Value":{"Apr 2020":6444.0},"Unit":"KG","Quantity":{"Apr 2020":50.0},"ReporterIso":"US","PartnerIso":"MY"}
{"Direction":"Export","Reporter":"United States","Partner":"Bahamas","Product":"7211900000","Currency":"USD","Value":{"Apr 2020":18982.0,"May 2020":9395.0,"Jul 2020":32950.0,"Aug 2020":5418.0,"Sep 2020":6264.0,"Mar 2020":2509.0,"Jan 2020":43000.0},"Unit":"KG","Quantity":{"Apr 2020":4340.0,"May 2020":2268.0,"Jul 2020":13436.0,"Aug 2020":816.0,"Sep 2020":3930.0,"Mar 2020":1673.0,"Jan 2020":7500.0},"ReporterIso":"US","PartnerIso":"BS"}
{"Direction":"Export","Reporter":"United States","Partner":"Canada","Product":"7208380050","Currency":"USD","Value":{"Apr 2020":256092.0,"May 2020":772685.0,"Oct 2020":247091.0,"Aug 2020":387656.0,"Jun 2020":478051.0,"Sep 2020":499619.0,"Jul 2020":262581.0,"Mar 2020":1025836.0,"Feb 2020":1039535.0,"Jan 2020":1085497.0},"Unit":"KG","Quantity":{"Apr 2020":392273.0,"May 2020":1179450.0,"Oct 2020":420557.0,"Aug 2020":554797.0,"Jun 2020":719479.0,"Sep 2020":766775.0,"Jul 2020":389713.0,"Mar 2020":1502052.0,"Feb 2020":1558654.0,"Jan 2020":1555007.0},"ReporterIso":"US","PartnerIso":"CA"}
{"Direction":"Export","Reporter":"United States","Partner":"Australia","Product":"7212300000","Currency":"USD","Value":{"Apr 2020":84672.0,"Oct 2020":84672.0,"Sep 2020":84672.0},"Unit":"KG","Quantity":{"Apr 2020":18280.0,"Oct 2020":17486.0,"Sep 2020":16050.0},"ReporterIso":"US","PartnerIso":"AU"}
{"Direction":"Export","Reporter":"United States","Partner":"World","Product":"7209181500","Currency":"USD","Value":{"Apr 2020":76086.0,"May 2020":191909.0,"Jun 2020":710605.0,"Oct 2020":432462.0,"Sep 2020":1013611.0,"Aug 2020":272684.0,"Jul 2020":230918.0,"Feb 2020":1230372.0,"Jan 2020":2199999.0,"Mar 2020":540141.0},"Unit":"KG","Quantity":{"Apr 2020":70157.0,"May 2020":190324.0,"Jun 2020":737183.0,"Oct 2020":450921.0,"Sep 2020":1103870.0,"Aug 2020":294551.0,"Jul 2020":230210.0,"Feb 2020":1402920.0,"Jan 2020":2500413.0,"Mar 2020":574072.0},"ReporterIso":"US","PartnerIso":"ZZ"}
{"Direction":"Export","Reporter":"United States","Partner":"World","Product":"7210300030","Currency":"USD","Value":{"Apr 2020":248474.0,"May 2020":290058.0,"Aug 2020":476878.0,"Jun 2020":2173416.0,"Sep 2020":891274.0,"Oct 2020":1340504.0,"Jul 2020":715408.0,"Mar 2020":1001145.0,"Feb 2020":2330647.0,"Jan 2020":1238682.0},"Unit":"KG","Quantity":{"Apr 2020":174656.0,"May 2020":220714.0,"Aug 2020":357792.0,"Jun 2020":1653715.0,"Sep 2020":757356.0,"Oct 2020":997720.0,"Jul 2020":549094.0,"Mar 2020":757039.0,"Feb 2020":1913940.0,"Jan 2020":1011018.0},"ReporterIso":"US","PartnerIso":"ZZ"}
{"Direction":"Export","Reporter":"United States","Partner":"Mexico","Product":"7208900000","Currency":"USD","Value":{"Apr 2020":133913.0,"May 2020":94912.0,"Aug 2020":14701.0,"Jun 2020":32926.0,"Oct 2020":110178.0,"Sep 2020":57811.0,"Jul 2020":15256.0,"Mar 2020":165529.0,"Feb 2020":37979.0,"Jan 2020":117790.0},"Unit":"KG","Quantity":{"Apr 2020":158740.0,"May 2020":73051.0,"Aug 2020":17502.0,"Jun 2020":30140.0,"Oct 2020":39209.0,"Sep 2020":74535.0,"Jul 2020":15245.0,"Mar 2020":158213.0,"Feb 2020":24055.0,"Jan 2020":117867.0},"ReporterIso":"US","PartnerIso":"MX"}
{"Direction":"Export","Reporter":"United States","Partner":"World","Product":"7209150000","Currency":"USD","Value":{"Apr 2020":45467.0,"May 2020":62193.0,"Jun 2020":229255.0,"Oct 2020":149490.0,"Sep 2020":223180.0,"Aug 2020":161432.0,"Jul 2020":143964.0,"Mar 2020":71112.0,"Feb 2020":203126.0,"Jan 2020":74483.0},"Unit":"KG","Quantity":{"Apr 2020":67301.0,"May 2020":72625.0,"Jun 2020":297458.0,"Oct 2020":190269.0,"Sep 2020":272584.0,"Aug 2020":177554.0,"Jul 2020":190961.0,"Mar 2020":100231.0,"Feb 2020":230604.0,"Jan 2020":110544.0},"ReporterIso":"US","PartnerIso":"ZZ"}
{"Direction":"Export","Reporter":"United States","Partner":"Mexico","Product":"7209160090","Currency":"USD","Value":{"Apr 2020":247689.0,"May 2020":233933.0,"Oct 2020":2925054.0,"Aug 2020":1388693.0,"Sep 2020":1995741.0,"Jun 2020":357085.0,"Jul 2020":1729441.0,"Mar 2020":1040466.0,"Feb 2020":1744073.0,"Jan 2020":1799316.0},"Unit":"KG","Quantity":{"Apr 2020":274906.0,"May 2020":239434.0,"Oct 2020":2768564.0,"Aug 2020":1353748.0,"Sep 2020":2333688.0,"Jun 2020":355508.0,"Jul 2020":1598431.0,"Mar 2020":945468.0,"Feb 2020":1440076.0,"Jan 2020":1493715.0},"ReporterIso":"US","PartnerIso":"MX"}
{"Direction":"Export","Reporter":"United States","Partner":"Canada","Product":"7212400000","Currency":"USD","Value":{"May 2020":275182.0,"Apr 2020":493240.0,"Aug 2020":350597.0,"Jun 2020":499062.0,"Sep 2020":555123.0,"Oct 2020":532179.0,"Jul 2020":697352.0,"Mar 2020":830001.0,"Feb 2020":1069681.0,"Jan 2020":542351.0},"Unit":"KG","Quantity":{"May 2020":192716.0,"Apr 2020":418674.0,"Aug 2020":271990.0,"Jun 2020":405857.0,"Sep 2020":422948.0,"Oct 2020":367652.0,"Jul 2020":577490.0,"Mar 2020":709423.0,"Feb 2020":942164.0,"Jan 2020":406760.0},"ReporterIso":"US","PartnerIso":"CA"}
{"Direction":"Export","Reporter":"United States","Partner":"World","Product":"7208270060","Currency":"USD","Value":{"May 2020":1247470.0,"Apr 2020":1810701.0,"Jun 2020":3526950.0,"Jul 2020":5666238.0,"Oct 2020":9886446.0,"Sep 2020":5467858.0,"Aug 2020":5330481.0,"Mar 2020":5230373.0,"Feb 2020":6059876.0,"Jan 2020":7152918.0},"Unit":"KG","Quantity":{"May 2020":1402777.0,"Apr 2020":2160374.0,"Jun 2020":3950147.0,"Jul 2020":6354063.0,"Oct 2020":13225057.0,"Sep 2020":6327093.0,"Aug 2020":6141498.0,"Mar 2020":6143402.0,"Feb 2020":7306145.0,"Jan 2020":8370076.0},"ReporterIso":"US","PartnerIso":"ZZ"}
{"Direction":"Export","Reporter":"United States","Partner":"Japan","Product":"7207190000","Currency":"USD","Value":{"May 2020":5334.0,"Aug 2020":10080.0},"Unit":"KG","Quantity":{"May 2020":590.0,"Aug 2020":101.0},"ReporterIso":"US","PartnerIso":"JP"}
{"Direction":"Export","Reporter":"United States","Partner":"World","Product":"7207190000","Currency":"USD","Value":{"Apr 2020":292027.0,"May 2020":276208.0,"Aug 2020":263398.0,"Jun 2020":279931.0,"Oct 2020":101698.0,"Sep 2020":23695.0,"Jul 2020":112959.0,"Mar 2020":140494.0,"Jan 2020":154490.0,"Feb 2020":314695.0},"Unit":"KG","Quantity":{"Apr 2020":128718.0,"May 2020":44732.0,"Aug 2020":33499.0,"Jun 2020":37576.0,"Oct 2020":30951.0,"Sep 2020":8995.0,"Jul 2020":30812.0,"Mar 2020":47052.0,"Jan 2020":16816.0,"Feb 2020":40188.0},"ReporterIso":"US","PartnerIso":"ZZ"}
{"Direction":"Export","Reporter":"United States","Partner":"World","Product":"7209170010","Currency":"USD","Value":{"Apr 2020":5364700.0,"May 2020":4318004.0,"Jul 2020":2454780.0,"Jun 2020":1603578.0,"Oct 2020":4873953.0,"Aug 2020":4023163.0,"Sep 2020":7053496.0,"Mar 2020":6913152.0,"Feb 2020":4384188.0,"Jan 2020":7205225.0},"Unit":"KG","Quantity":{"Apr 2020":6139902.0,"May 2020":4713113.0,"Jul 2020":2721364.0,"Jun 2020":1645743.0,"Oct 2020":5760823.0,"Aug 2020":4587277.0,"Sep 2020":8209674.0,"Mar 2020":8171241.0,"Feb 2020":4972772.0,"Jan 2020":8177022.0},"ReporterIso":"US","PartnerIso":"ZZ"}
{"Direction":"Export","Reporter":"United States","Partner":"World","Product":"7207110000","Currency":"USD","Value":{"Apr 2020":27681.0,"May 2020":1659154.0,"Jun 2020":321634.0,"Oct 2020":70934.0,"Jul 2020":901180.0,"Aug 2020":544576.0,"Jan 2020":393229.0,"Mar 2020":54686.0,"Feb 2020":241381.0},"Unit":"KG","Quantity":{"Apr 2020":21575.0,"May 2020":413467.0,"Jun 2020":76326.0,"Oct 2020":14656.0,"Jul 2020":1768512.0,"Aug 2020":1211707.0,"Jan 2020":290503.0,"Mar 2020":10278.0,"Feb 2020":40162.0},"ReporterIso":"US","PartnerIso":"ZZ"}
{"Direction":"Export","Reporter":"United States","Partner":"Jamaica","Product":"7208102500","Currency":"USD","Value":{"May 2020":6468.0},"Unit":"KG","Quantity":{"May 2020":7982.0},"ReporterIso":"US","PartnerIso":"JM"}
{"Direction":"Export","Reporter":"United States","Partner":"Ireland","Product":"7207190000","Currency":"USD","Value":{"May 2020":10059.0},"Unit":"KG","Quantity":{"May 2020":1593.0},"ReporterIso":"US","PartnerIso":"IE"}

Re: Import a JSONL File?

AhmedAl_Attar — Sun, 10 Jan 2021 16:16:05 GMT

@BhargavDesai

So, I saved your new JSONL data lines into a .jsonl file and used the following new code on my SAS University Edition

filename jsonl "/folders/myfolders/sample.jsonl"; /* This contains your original JSONL lines */
filename json "/folders/myfolders/sample.json"; /* This will contain the JSON version of the file */
filename map temp;

data _null_;
	infile jsonl end=eof;
	file json;
	put '[{"records":['; /* Required for JSON formatting */
	do until (eof);
		input;
		line = STRIP(_infile_);
		line = tranwrd(line,'} {','}, {'); /* Add ',' between '} {' */
		put line;
	end;
	put ']}]'; /* Required for JSON formatting */
	stop;
run;

libname json json automap=create map=map;
proc print data=json.records; run;
proc print data=json.records_quantity; run;
proc print data=json.records_value; run;

It looks like:

json.records_quantity: 10 months Time-Line data of quantities exported in KG
json.records_value: 10 months Time-Line data of value in USD

The approach to join and link these data sets will differ based on what you want to do with the data and how to analyze it

Hope this helps,

Ahmed

Re: Import a JSONL File?

brucegilsen — Tue, 12 Jan 2021 18:25:08 GMT

SAS Dummy article is here:

https://blogs.sas.com/content/sasdummy/2018/11/14/jsonl-with-proc-json/

topic Re: Import a JSONL File? in SAS Programming

Import a JSONL File?

Re: Import a JSONL File?

Re: Import a JSONL File?

Re: Import a JSONL File?

Re: Import a JSONL File?

Re: Import a JSONL File?

Re: Import a JSONL File?