Hello,

Is there a SAS library that makes it possible to use a web API that only returns data in the JSON format?

asked 01 Dec '10, 19:19

Dan's gravatar image

Dan
2314
accept rate: 0%


No. I asked the same around a year ago and that was the answer.

Consider having SAS call a .NET or Java library, convert the data, then pump it back to SAS.

If SAS has something now that supports it, I would love to know.

link

answered 01 Dec '10, 19:33

Alan%20Churchill's gravatar image

Alan Churchill
26922
accept rate: 44%

I don't think there's a need to call out to a middle layer.

Why not just use the URL access method and pull the JSON in and parse it? JSON is a fairly straight forward format, it obviously won't be an object in SAS, but could easily be represented in a dataset (or two :)

link

answered 02 Dec '10, 14:49

jay.l.stevens's gravatar image

jay.l.stevens ♦
1.0k624
accept rate: 36%

i'd be very interested in seeing a complete SAS solution for a simple json feed like this

the docs are here

(03 Dec '10, 02:11) rkoopmann

Pure SAS JSON

All, this is a totally naive implementation of a pure SAS JSON parser . If you feed it a FILENAME ref pointing to a URL that generates JSON (preferrably on one line ... :) and a variable list (right now it has to be IN THE ORDER it shows up in the JSON). Its relatively robust for simple single- and multi-row datasets. Hierarchical data is not supported. You could probably take this and run with it to the hills and beyond if you wanted.

In any case, the OP test JSON is parsed perfectly. In addition, you can call a normal Facebook Graph API url for an ID and get that data into a dataset as well. Output is a SAS dataset named "JSON" (shock).

%macro JSONParse(Filename, varList);
data JSON (keep=&varlist record);
    infile &FILENAME ;
    input;
    ExpressionID = prxparse('/{(.*?)}/');
    jsontext = _INFILE_;
    start = 1;
    stop = length(jsontext);

    array vlist $50 &Varlist;

   call prxnext(ExpressionID, start, stop, jsontext, position, length);

    do while (position > 0);
        newpos = position + 1;
        newlen = length - 2;
        record = substr(jsontext, newpos, newlen);

        i=0;    
        do over vlist;
            i+1;
            vlist = strip(compress(scan(scan( record, i, ","), 2, ":"), '"'));
        end;

        output;
        call prxnext(ExpressionID, start, stop, jsontext, position, length);
    end;
run;
%mend;

Examples of calling it

using the originally posted test json string

FILENAME TEST "test.json";
%JSONParse(TEST, id password);

This results in 3 records with the data specified in the OP sample.

using Facebook Graph API

FILENAME TEST URL "http://graph.facebook.com/jay.l.stevens/";
%JSONParse(TEST, id name first_name last_name link username gender locale);

This results in one record with all of my facebook public info.

As I've said, it's naive, assumes a lot via convention, needs to be expanded and more dynamic, etc. But for now, it handles BASIC json parsing.

link

answered 21 Dec '11, 18:00

jay.l.stevens's gravatar image

jay.l.stevens ♦
1.0k624
accept rate: 36%

i think this can be solved with a JSON -> PERL -> XML -> SAS workflow?

link

answered 02 Dec '10, 12:27

rkoopmann's gravatar image

rkoopmann
745
accept rate: 14%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×1
×1

Asked: 01 Dec '10, 19:19

Seen: 2,807 times

Last updated: 21 Dec '11, 18:00

Related questions

powered by OSQA