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

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

yo1 — Wed, 14 Dec 2016 21:15:20 GMT

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

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

BrunoMueller — Wed, 14 Dec 2016 22:25:20 GMT

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

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

Ksharp — Thu, 15 Dec 2016 07:51:13 GMT

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;

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

rogerjdeangelis — Thu, 15 Dec 2016 16:58:52 GMT

You might want to take a look at

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

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

yo1 — Thu, 15 Dec 2016 17:00:09 GMT

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.

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

yo1 — Thu, 15 Dec 2016 17:01:24 GMT

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

Best

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

yo1 — Thu, 15 Dec 2016 17:02:31 GMT

Hello,

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

Best