topic SAS EG - how to read in JSON file in SAS Programming

SAS EG - how to read in JSON file

BhargavDesai — Fri, 08 Jan 2021 17:02:49 GMT

Hello

Happy New Year!

I am in need of reading JSON file with following format. How can I read in? This is totally new to me (reading Json file)

Thanks

{"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"}
{"Direction":"Export","Reporter":"United States","Partner":"World","Product":"7207120000","Currency":"USD","Value":{"May 2020":266578.0,"Apr 2020":358411.0,"Aug 2020":181567.0,"Jun 2020":571815.0,"Oct 2020":168009.0,"Jul 2020":218665.0,"Sep 2020":1121342.0,"Mar 2020":253274.0,"Jan 2020":80264.0,"Feb 2020":354798.0},"Unit":"KG","Quantity":{"May 2020":155632.0,"Apr 2020":42071.0,"Aug 2020":20047.0,"Jun 2020":127524.0,"Oct 2020":31575.0,"Jul 2020":41135.0,"Sep 2020":2833757.0,"Mar 2020":248445.0,"Jan 2020":28522.0,"Feb 2020":504091.0},"ReporterIso":"US","PartnerIso":"ZZ"}
{"Direction":"Export","Reporter":"United States","Partner":"Mexico","Product":"7207110000","Currency":"USD","Value":{"May 2020":1411231.0,"Oct 2020":44333.0,"Jun 2020":80880.0,"Mar 2020":21870.0,"Jan 2020":154894.0},"Unit":"KG","Quantity":{"May 2020":319639.0,"Oct 2020":1398.0,"Jun 2020":7731.0,"Mar 2020":4754.0,"Jan 2020":253095.0},"ReporterIso":"US","PartnerIso":"MX"}
{"Direction":"Export","Reporter":"United States","Partner":"Austria","Product":"7208260030","Currency":"USD","Value":{"May 2020":5000.0},"Unit":"KG","Quantity":{"May 2020":7011.0},"ReporterIso":"US","PartnerIso":"AT"}
{"Direction":"Export","Reporter":"United States","Partner":"Venezuela","Product":"7208102500","Currency":"USD","Value":{"May 2020":19200.0},"Unit":"KG","Quantity":{"May 2020":13172.0},"ReporterIso":"US","PartnerIso":"VE"}
{"Direction":"Export","Reporter":"United States","Partner":"Bermuda","Product":"7212400000","Currency":"USD","Value":{"May 2020":33385.0,"Aug 2020":19489.0,"Oct 2020":8762.0,"Sep 2020":34145.0},"Unit":"KG","Quantity":{"May 2020":15542.0,"Aug 2020":3116.0,"Oct 2020":1501.0,"Sep 2020":7315.0},"ReporterIso":"US","PartnerIso":"BM"}
{"Direction":"Export","Reporter":"United States","Partner":"World","Product":"7208540000","Currency":"USD","Value":{"May 2020":969382.0,"Apr 2020":842232.0,"Aug 2020":2141053.0,"Jun 2020":1222413.0,"Oct 2020":2218406.0,"Sep 2020":2057501.0,"Jul 2020":1395100.0,"Mar 2020":1904728.0,"Jan 2020":1305126.0,"Feb 2020":1410795.0},"Unit":"KG","Quantity":{"May 2020":1121083.0,"Apr 2020":876884.0,"Aug 2020":2624770.0,"Jun 2020":1550987.0,"Oct 2020":2442440.0,"Sep 2020":2408761.0,"Jul 2020":1612504.0,"Mar 2020":1920154.0,"Jan 2020":1201447.0,"Feb 2020":1402923.0},"ReporterIso":"US","PartnerIso":"ZZ"}
{"Direction":"Export","Reporter":"United States","Partner":"World","Product":"7208101500","Currency":"USD","Value":{"May 2020":171166.0,"Apr 2020":185897.0,"Aug 2020":163057.0,"Jun 2020":156907.0,"Oct 2020":457348.0,"Jul 2020":264745.0,"Sep 2020":426419.0,"Mar 2020":384946.0,"Feb 2020":529878.0,"Jan 2020":191591.0},"Unit":"KG","Quantity":{"May 2020":190479.0,"Apr 2020":202486.0,"Aug 2020":160361.0,"Jun 2020":119590.0,"Oct 2020":472462.0,"Jul 2020":302606.0,"Sep 2020":427088.0,"Mar 2020":424184.0,"Feb 2020":498805.0,"Jan 2020":179402.0},"ReporterIso":"US","PartnerIso":"ZZ"}
{"Direction":"Export","Reporter":"United States","Partner":"Mexico","Product":"7208380050","Currency":"USD","Value":{"May 2020":528641.0,"Apr 2020":314415.0,"Oct 2020":989879.0,"Jun 2020":73599.0,"Sep 2020":457484.0,"Aug 2020":13621.0,"Jan 2020":49680.0,"Feb 2020":115478.0,"Jul 2020":196206.0,"Mar 2020":57781.0},"Unit":"KG","Quantity":{"May 2020":826152.0,"Apr 2020":466956.0,"Oct 2020":1330244.0,"Jun 2020":101127.0,"Sep 2020":638750.0,"Aug 2020":20031.0,"Jan 2020":70729.0,"Feb 2020":152996.0,"Jul 2020":288538.0,"Mar 2020":79786.0},"ReporterIso":"US","PartnerIso":"MX"}
{"Direction":"Export","Reporter":"United States","Partner":"Canada","Product":"7208530000","Currency":"USD","Value":{"Apr 2020":222776.0,"May 2020":145143.0,"Aug 2020":102907.0,"Jun 2020":237471.0,"Oct 2020":248167.0,"Sep 2020":126885.0,"Jul 2020":142916.0,"Mar 2020":286637.0,"Feb 2020":179056.0,"Jan 2020":172947.0},"Unit":"KG","Quantity":{"Apr 2020":310521.0,"May 2020":188273.0,"Aug 2020":98895.0,"Jun 2020":302443.0,"Oct 2020":318419.0,"Sep 2020":169642.0,"Jul 2020":199190.0,"Mar 2020":378126.0,"Feb 2020":241838.0,"Jan 2020":238127.0},"ReporterIso":"US","PartnerIso":"CA"}
{"Direction":"Export","Reporter":"United States","Partner":"Brazil","Product":"7212400000","Currency":"USD","Value":{"May 2020":56165.0,"Oct 2020":142911.0,"Aug 2020":113381.0,"Sep 2020":71320.0,"Mar 2020":158364.0,"Feb 2020":149649.0,"Jan 2020":52144.0},"Unit":"KG","Quantity":{"May 2020":9238.0,"Oct 2020":29937.0,"Aug 2020":25407.0,"Sep 2020":14281.0,"Mar 2020":31399.0,"Feb 2020":37182.0,"Jan 2020":11346.0},"ReporterIso":"US","PartnerIso":"BR"}
{"Direction":"Export","Reporter":"United States","Partner":"Germany","Product":"7208520000","Currency":"USD","Value":{"May 2020":3304.0},"Unit":"KG","Quantity":{"May 2020":664.0},"ReporterIso":"US","PartnerIso":"DE"}
{"Direction":"Export","Reporter":"United States","Partner":"Canada","Product":"7207190000","Currency":"USD","Value":{"May 2020":28756.0,"Apr 2020":22587.0,"Aug 2020":34722.0,"Jun 2020":21097.0,"Sep 2020":15969.0,"Oct 2020":53269.0,"Jul 2020":53173.0,"Mar 2020":40214.0,"Feb 2020":83009.0,"Jan 2020":3161.0},"Unit":"KG","Quantity":{"May 2020":5598.0,"Apr 2020":3507.0,"Aug 2020":6000.0,"Jun 2020":5737.0,"Sep 2020":7173.0,"Oct 2020":24902.0,"Jul 2020":18607.0,"Mar 2020":18087.0,"Feb 2020":23986.0,"Jan 2020":632.0},"ReporterIso":"US","PartnerIso":"CA"}
{"Direction":"Export","Reporter":"United States","Partner":"Cayman Islands","Product":"7207200000","Currency":"USD","Value":{"May 2020":6399.0},"Unit":"KG","Quantity":{"May 2020":2109.0},"ReporterIso":"US","PartnerIso":"KY"}
{"Direction":"Export","Reporter":"United States","Partner":"United Kingdom","Product":"7207190000","Currency":"USD","Value":{"May 2020":22586.0,"Jun 2020":6917.0,"Jul 2020":11785.0},"Unit":"KG","Quantity":{"May 2020":306.0,"Jun 2020":2000.0,"Jul 2020":606.0},"ReporterIso":"US","PartnerIso":"GB"}
{"Direction":"Export","Reporter":"United States","Partner":"World","Product":"7206900000","Currency":"USD","Value":{"May 2020":699234.0,"Apr 2020":792807.0,"Jun 2020":1109210.0,"Oct 2020":1237245.0,"Sep 2020":845570.0,"Aug 2020":2546637.0,"Jul 2020":1177278.0,"Mar 2020":1820157.0,"Feb 2020":1014785.0,"Jan 2020":1377205.0},"Unit":"KG","Quantity":{"May 2020":256560.0,"Apr 2020":301147.0,"Jun 2020":534017.0,"Oct 2020":152154.0,"Sep 2020":218618.0,"Aug 2020":903426.0,"Jul 2020":192422.0,"Mar 2020":857988.0,"Feb 2020":206662.0,"Jan 2020":719990.0},"ReporterIso":"US","PartnerIso":"ZZ"}
{"Direction":"Export","Reporter":"United States","Partner":"Italy","Product":"7206900000","Currency":"USD","Value":{"May 2020":14641.0,"Oct 2020":3236.0},"Unit":"KG","Quantity":{"May 2020":452.0,"Oct 2020":3719.0},"ReporterIso":"US","PartnerIso":"IT"}

Re: SAS EG - how to read in JSON file

Tom — Fri, 08 Jan 2021 17:09:03 GMT

Does not look like a JSON file to me. Might the JSON lines (JSONL) instead.

Search this site for earlier questions that show how to convert an JSONL file into a JSON file so it can be read.

Re: SAS EG - how to read in JSON file

BhargavDesai — Fri, 08 Jan 2021 17:19:51 GMT

the file i received has extension JSON

Re: SAS EG - how to read in JSON file

ballardw — Fri, 08 Jan 2021 17:28:33 GMT

@BhargavDesai wrote:

the file i received has extension JSON

And I have received files with a TXT extension that were actually in Word DOC format.

Names can hide a multitude of sins.

Re: SAS EG - how to read in JSON file

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

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;

This code works and read in file; the code creates datasets including Records, records_quantity and Record_value. How to I join these datastes to make it one table?

Re: SAS EG - how to read in JSON file

Tom — Fri, 08 Jan 2021 20:11:42 GMT

You probably need to understand how the data is actually organized. I looks to me like you have three tables ROOT, VALUE, and QUANTITY.

You can combine ROOT with either of the other two by the key variable ordinal_root.

But to combine the other two you will need to either restructure them or rename some of the variables.

214   proc sql ;
215   create table want as
216   select *
217   from json.root
218     left join json.QUANTITY
219     on root.ordinal_root = QUANTITY.ordinal_root
220     left join json.value
221     on root.ordinal_root = value.ordinal_root
222   ;
WARNING: Variable ordinal_root already exists on file WORK.WANT.
WARNING: Variable ordinal_root already exists on file WORK.WANT.
WARNING: Variable Apr_2020 already exists on file WORK.WANT.
WARNING: Variable May_2020 already exists on file WORK.WANT.
WARNING: Variable Aug_2020 already exists on file WORK.WANT.
WARNING: Variable Jun_2020 already exists on file WORK.WANT.
WARNING: Variable Oct_2020 already exists on file WORK.WANT.
WARNING: Variable Sep_2020 already exists on file WORK.WANT.
WARNING: Variable Jul_2020 already exists on file WORK.WANT.
WARNING: Variable Feb_2020 already exists on file WORK.WANT.
WARNING: Variable Mar_2020 already exists on file WORK.WANT.
WARNING: Variable Jan_2020 already exists on file WORK.WANT.
NOTE: Table WORK.WANT created, with 20 rows and 21 columns.

223   quit;

Re: SAS EG - how to read in JSON file

Tom — Fri, 08 Jan 2021 20:43:39 GMT

You don't NEED to define that RECORDS tag in your generated JSON file. Plus if you put the continuation character (comma in this case) at the beginning of the line instead of the end of the line before then the resulting text file will be much easier for humans to scan.

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

[{"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: SAS EG - how to read in JSON file

Ksharp — Sat, 09 Jan 2021 12:51:08 GMT

Write some data step code ?

data temp;
infile 'c:\temp\have.txt' dlm='{},';
input x : $200. @@;
length _vname $ 200;
retain _vname;
vname=dequote(scan(x,1,':'));
value=dequote(scan(x,-1,':'));
if missing(value) then _vname=vname;
if find(strip(vname),' ') then vname=catx(' ',_vname,vname);
if vname='Direction' then group+1;
run;
proc transpose data=temp out=want ;
by group;
id vname;
var value;
run;

Re: SAS EG - how to read in JSON file

BhargavDesai — Mon, 11 Jan 2021 18:47:00 GMT

Is your program looking for TXT file?

data temp;

infile 'c:\temp\have.txt' dlm='{},';

input x : $200. @@;

length _vname $ 200;

retain _vname;

vname=dequote(scan(x,1,':'));

value=dequote(scan(x,-1,':'));

if missing(value) then _vname=vname;

if find(strip(vname),' ') then vname=catx(' ',_vname,vname);

if vname='Direction' then group+1;

run;

proc transpose data=temp out=want ;

by group;

id vname;

var value;

run;

Re: SAS EG - how to read in JSON file

Tom — Mon, 11 Jan 2021 18:52:52 GMT

A JSONL file IS a text file.

Re: SAS EG - how to read in JSON file

Ksharp — Tue, 12 Jan 2021 10:12:44 GMT

As Tom said, Just replace
infile 'c:\temp\have.txt'
with JSON file:
infile 'c:\temp\have.json '

Re: SAS EG - how to read in JSON file

brucegilsen — Thu, 28 Jan 2021 22:03:43 GMT

Tom, this code is very helpful. One small suggestion is to move the INPUT statement before the IF/THEN/ELSE statement. As written, it puts an extra record with just a comma at the end of the output file because the IF/THEN/ELSE statement executes one last time after the IF EOF statement. I think this is because the INPUT statement after END= is what triggers the termination of the DATA step.

Re: SAS EG - how to read in JSON file

Tom — Thu, 28 Jan 2021 23:41:14 GMT

That sounds right.