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"}
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.
the file i received has extension JSON
@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.
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?
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;
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"} ]
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.
That sounds right.
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;
A JSONL file IS a text file.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.