SAS EG - how to read in JSON file

BhargavDesai · Posted 01-08-2021 12:02 PM

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

Tom · Posted 01-08-2021 12:09 PM

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.

BhargavDesai · Posted 01-08-2021 12:19 PM

the file i received has extension JSON

ballardw · Posted 01-08-2021 12:28 PM

@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.

BhargavDesai · Posted 01-08-2021 01:59 PM

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?

Tom · Posted 01-08-2021 03:11 PM

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;

Tom · Posted 01-08-2021 03:42 PM

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

brucegilsen · Posted 01-28-2021 05:03 PM

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.

Tom · Posted 01-28-2021 06:41 PM

That sounds right.

Ksharp · Posted 01-09-2021 07:51 AM

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;

BhargavDesai · Posted 01-11-2021 01:47 PM

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;

Tom · Posted 01-11-2021 01:52 PM

A JSONL file IS a text file.

Ksharp · Posted 01-12-2021 05:12 AM

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

SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Re: SAS EG - how to read in JSON file

Classroom Training Available!