DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor yo1
Occasional Contributor
Posts: 19
Accepted Solution

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

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

 "

 "

 "

"

 "

 "

 "

"


Accepted Solutions
Solution
‎12-15-2016 11:58 AM
Super User
Posts: 10,023

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

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;

View solution in original post


All Replies
SAS Super FREQ
Posts: 708

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

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

Occasional Contributor yo1
Occasional Contributor
Posts: 19

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

Posted in reply to Bruno_SAS

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.

Solution
‎12-15-2016 11:58 AM
Super User
Posts: 10,023

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

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;
Valued Guide
Posts: 505

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

 

You might want to take a look at 

 

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

Occasional Contributor yo1
Occasional Contributor
Posts: 19

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

Posted in reply to rogerjdeangelis

Hello,

 

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

 

Best

Occasional Contributor yo1
Occasional Contributor
Posts: 19

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

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

 

Best                    

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 617 views
  • 3 likes
  • 4 in conversation