Need to create a SAS dataset from a JSON file- its in text format.

[yo1]

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

[BrunoMueller]

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

[yo1]

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.

[Ksharp · Accepted solution]

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;

[rogerjdeangelis]

You might want to take a look at 

https://listserv.uga.edu/cgi-bin/wa?A2=SAS-L;36aeae18.1612c

[yo1]

Hello,

the URL provided didn't seem to work.  It directed me to a login page for some reason. 

Best

[yo1]

Thanks Ksharp.   The code you provided not only provided the output but gave me a better understanding on how to approach these situaitons. 

Best