I am trying to read JSON data in a data step. I am running SAS 9.3, so I don't have PROC JSON. Below is my code, results, and text file I am trying to read. The problem is that I am using scanover and @ to point to "year":, for instance, and read the next variable. However, when I get to periodName, it has variable length, so I am running past the field. I have experimented with different options, dlm=, etc, but I can't get this to work. Any help would be greatly appreciated!
data testdata;
infile OUT lrecl = 32000 truncover scanover;
input
@'"year":' year $6.
@'"period":' period $5.
@'"periodName":' periodName $15.
@'"value":' value $15.
@'"footnotes":' footnotes $255.;
run;
"2014" | "M10" | "October","valu | "24.19","footno | [{}]},{"year":"2014","period":"M07","periodName":"July","value":"23.96","footnotes":[{}]},{"year":"2014","period":"M06","periodName":"June","value":"24.11","footnotes":[{}]},{"year":"2014","period":"M05","periodName":"May","value":"23.98","footnotes":[{}] |
{"status":"REQUEST_SUCCEEDED","responseTime":42,"message":[],"Results":{
"series":
[{"seriesID":"CEU0800000003","data":[{"year":"2014","period":"M10","periodName":"October","value":"24.19","footnotes":[{"code":"P","text":"preliminary"}]},{"year":"2014","period":"M09","periodName":"September","value":"24.19","footnotes":[{"code":"P","text":"preliminary"}]},{"year":"2014","period":"M08","periodName":"August","value":"23.98","footnotes":[{}]},{"year":"2014","period":"M07","periodName":"July","value":"23.96","footnotes":[{}]},{"year":"2014","period":"M06","periodName":"June","value":"24.11","footnotes":[{}]},{"year":"2014","period":"M05","periodName":"May","value":"23.98","footnotes":[{}]},{"year":"2014","period":"M04","periodName":"April","value":"24.08","footnotes":[{}]},{"year":"2014","period":"M03","periodName":"March","value":"24.21","footnotes":[{}]},{"year":"2014","period":"M02","periodName":"February","value":"24.32","footnotes":[{}]},{"year":"2014","period":"M01","periodName":"January","value":"24.06","footnotes":[{}]},{"year":"2013","period":"M12","periodName":"December","value":"23.99","footnotes":[{}]},{"year":"2013","period":"M11","periodName":"November","value":"23.81","footnotes":[{}]},{"year":"2013","period":"M10","periodName":"October","value":"23.74","footnotes":[{}]},{"year":"2013","period":"M09","periodName":"September","value":"23.89","footnotes":[{}]},{"year":"2013","period":"M08","periodName":"August","value":"23.48","footnotes":[{}]},{"year":"2013","period":"M07","periodName":"July","value":"23.49","footnotes":[{}]},{"year":"2013","period":"M06","periodName":"June","value":"23.66","footnotes":[{}]},{"year":"2013","period":"M05","periodName":"May","value":"23.51","footnotes":[{}]},{"year":"2013","period":"M04","periodName":"April","value":"23.66","footnotes":[{}]},{"year":"2013","period":"M03","periodName":"March","value":"23.58","footnotes":[{}]},{"year":"2013","period":"M02","periodName":"February","value":"23.66","footnotes":[{}]},{"year":"2013","period":"M01","periodName":"January","value":"23.62","footnotes":[{}]},{"year":"2012","period":"M12","periodName":"December","value":"23.58","footnotes":[{}]},{"year":"2012","period":"M11","periodName":"November","value":"23.31","footnotes":[{}]},{"year":"2012","period":"M10","periodName":"October","value":"23.24","footnotes":[{}]},{"year":"2012","period":"M09","periodName":"September","value":"23.42","footnotes":[{}]},{"year":"2012","period":"M08","periodName":"August","value":"22.96","footnotes":[{}]},{"year":"2012","period":"M07","periodName":"July","value":"23.20","footnotes":[{}]},{"year":"2012","period":"M06","periodName":"June","value":"22.97","footnotes":[{}]},{"year":"2012","period":"M05","periodName":"May","value":"23.04","footnotes":[{}]},{"year":"2012","period":"M04","periodName":"April","value":"23.36","footnotes":[{}]},{"year":"2012","period":"M03","periodName":"March","value":"23.10","footnotes":[{}]},{"year":"2012","period":"M02","periodName":"February","value":"23.12","footnotes":[{}]},{"year":"2012","period":"M01","periodName":"January","value":"23.30","footnotes":[{}]}]}]
}}
Try adding DLM=',' to the INFILE and using INFORMAT statements instead of having the informat in the input:
informat year $6.
period $5.
periodName $15.
value $15.
footnotes $255.;
input
@'"year":' year
@'"period":' period
@'"periodName":' periodName
@'"value":' value $15.
@'"footnotes":' footnotes ;
Try adding DLM=',' to the INFILE and using INFORMAT statements instead of having the informat in the input:
informat year $6.
period $5.
periodName $15.
value $15.
footnotes $255.;
input
@'"year":' year
@'"period":' period
@'"periodName":' periodName
@'"value":' value $15.
@'"footnotes":' footnotes ;
Thanks! I added a @@ to the end of the input, and it worked great.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.