Hello everyone,
I am currently working with a new site's API. I have the JSON file but ,sence this is a new website, its in a different format from a previous post. I need to convert this JSON file, that is in text format, into a SAS dataset. Attached is the JSON text file.
Here is the code that I used to process this file.
filename out "X:\BLSSeriesDataOut.txt" recfm=v lrecl=32000;
data work.pulljson;
infile out dsd lrecl=300000000 dlm='[]{},:';
input x : $2000. @@;
retain flag;
if x ='seriesID' then flag=1;
if x='footnotes' then group+1;
if flag and x not in (' ' 'seriesID' 'footnotes');
run;
(not sure how to continue )
Here is the example output format of what I want to acheive.
YEAR | Period | PeriodName | Value |
1999 | M12 | December | 1520 |
1999 | " | " | 5200 |
" | " | " | 2200 |
" | " | " | " |
" | " | " | " |
data x;
infile '/folders/myfolders/BLSSeriesDataOut.txt' dsd lrecl=30000000 dlm='{}[]:,';
input x : $2000.@@;
run;
data temp;
merge x x(firstobs=2 rename=(x=_x));
if lowcase(x) in ("year","period","periodname","value");
run;
data temp;
set temp;
if lowcase(x)='year' then group+1;
run;
proc transpose data=temp out=want(drop=_:);
by group;
id x;
var _x;
run;
Hi
You can make use of the @'someText' column pointer capability to read in this data. In case you have SAS9.4M4 you can use the JSON libname engine. Both techniques are shown below. To test out the JSON libname engine, you can download the latest version of the SAS University Edition, it comes with SAS9.4M4
filename fjson "c:\temp\BLSSeriesDataOut.json";
/*in case you have SAS9.4M4*/
/*libname ljson json FILEREF=fjson;*/
/**/
/*proc copy in=ljson out=work;*/
/*run;*/
data ds_data;
infile fjson lrecl=99999999 dlm="," dsd;
input
@'"year":' year : 4.
@'"period":' period : $3.
@'"periodName":' periodName : $32.
@'"value":' value : 8.
@@
;
run;
Bruno
Thanks Burno. This code worked for me. On my end I don't have the availablity of PROC GROOVY or PROC JSON. I have to work within the context of the DATA step. However, with everyone help I am getting better. Thanks.
data x;
infile '/folders/myfolders/BLSSeriesDataOut.txt' dsd lrecl=30000000 dlm='{}[]:,';
input x : $2000.@@;
run;
data temp;
merge x x(firstobs=2 rename=(x=_x));
if lowcase(x) in ("year","period","periodname","value");
run;
data temp;
set temp;
if lowcase(x)='year' then group+1;
run;
proc transpose data=temp out=want(drop=_:);
by group;
id x;
var _x;
run;
Hello,
the URL provided didn't seem to work. It directed me to a login page for some reason.
Best
Thanks Ksharp. The code you provided not only provided the output but gave me a better understanding on how to approach these situaitons.
Best
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.