Getting JSON from a web API

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 :)

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.

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