cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mdavidson
Quartz | Level 8 mdavidson
Quartz | Level 8
Go to Solution

Import a JSONL File?

Posted 06-23-2020 09:53 AM (2905 views)

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.

example.zip
0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
AhmedAl_Attar
Ammonite | Level 13 AhmedAl_Attar
Ammonite | Level 13

Re: Import a JSONL File?

Posted 06-23-2020 12:32 PM (2839 views)  |   In reply to mdavidson

@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

View solution in original post

6 REPLIES 6
AhmedAl_Attar
Ammonite | Level 13 AhmedAl_Attar
Ammonite | Level 13

Re: Import a JSONL File?

Posted 06-23-2020 12:32 PM (2840 views)  |   In reply to mdavidson

@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

BhargavDesai
Calcite | Level 5 BhargavDesai
Calcite | Level 5

Re: Import a JSONL File?

Posted 01-08-2021 01:56 PM (2618 views)  |   In reply to AhmedAl_Attar

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"}

0 Likes
AhmedAl_Attar
Ammonite | Level 13 AhmedAl_Attar
Ammonite | Level 13

Re: Import a JSONL File?

Posted 01-10-2021 11:16 AM (2598 views)  |   In reply to BhargavDesai

@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

0 Likes
Ksharp
Super User Ksharp
Super User

Re: Import a JSONL File?

Posted 06-24-2020 07:59 AM (2778 views)  |   In reply to mdavidson

@ChrisHemedinger  wrote a blog about it before.

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

0 Likes
BhargavDesai
Calcite | Level 5 BhargavDesai
Calcite | Level 5

Re: Import a JSONL File?

Posted 01-08-2021 01:52 PM (2620 views)  |   In reply to Ksharp
can't find JASONL blog on that site.
0 Likes

sasinnovate.png



April 27 – 30 | Gaylord Texan | Grapevine, Texas

Registration is open

Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and lock in 2025 pricing—just $495!

Register now

Call for Content EXTENDED

Still thinking about your presentation idea? The submission deadline has been extended to Friday, Nov. 14, at 11:59 p.m. ET.

Submit your proposal!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • ‎06-23-2020 09:53 AM
  • 2906 views
  • 2 likes
  • 5 in conversation