Blogs

Blogs

Jedi SAS Tricks: DS2 & APIs - GET the data you are looking for

11
By Topics | Learn SAS

While perusing the SAS 9.4 DS2 documentation, I ran across the section on the HTTP package. This intrigued me because, as DS2 has no text file handling statements I assumed all hope of leveraging Internet-based APIs was lost. But even a Jedi is wrong now and then! And what better API to test my API-wielding skills than the Star Wars API (SWAPI)?
The SWAPI site shows sample API calls to return a list of all people in the database (http://swapi.co/api/people), and another that returns information about Luke Skywalker (http://swapi.co/api/people/1). I’ll use these to test the HTTP package in DS2. Here’s my test code:

proc ds2 ;
    data _null_;
        dcl package logger putlog();
        dcl varchar(32767) character set utf8 url response;
        /* A user defined method to leverage the HTTP package for GETs */
        method GetResponse(varchar(32767) url);
            dcl integer i rc;
            dcl package http webQuery();
            /* create a GET call to the API*/
            webQuery.createGetMethod(url);
            /* execute the GET */
            webQuery.executeMethod();
            /* retrieve the response body as a string */
            webQuery.getResponseBodyAsString(response, rc);
        end;
        method run();
            /* Construct a GET URL to obtain a list of all people */
            url='http://swapi.co/api/people';
            GetResponse(url);
            put;
            putlog.log('N', URL);
            putlog.log('N', Response);
            put;
            /* Construct a GET URL to obtain information for Luke Skywalker (Person 1) */
            url='http://swapi.co/api/people/1';
            GetResponse(url);
            put;
            putlog.log('N', URL);
            putlog.log('N', Response);
            put;
        end;
    enddata;
    run;
quit;

The results in the log indicate that the API calls are working! Here is a partial SAS log for the first GET:

NOTE: http://swapi.co/api/people
NOTE: {"count":82,"next":"http://swapi.co/api/people/?page=2", "previous":null,"results":
{"name":"Luke Skywalker","height":"172" ,"mass":"77", "hair_color":"blond",
"skin_color":"fair","eye_color":"blue","birth_year":"19BBY","gender":"male",
"homeworld":"http://swapi.co/api/planets/1/",…

And a partial SAS log for the second GET:
NOTE: http://swapi.co/api/people/1
NOTE: {"name":"Luke kywalker","height":"172","mass":"77","hair_color":"blond" 
,"skin_color":"fair","eye_color":"blue","birth_year":"19BBY",
"gender":"male","homeworld":"http://swapi.co/api/planets/1/",…

The results are returned in JSON. SAS doesn't yet have an engine for reading JSON, so I’ll parse out the data using traditional text manipulation functions like SCAN and SUBSTR to build my CastOfCharacters dataset. First I'll need to know how many entries are in the API’s people database. The first URL contained this information early in the response string:
{"count":82,"

And I can extract that number using SCAN, then use it in a DO loop to execute one query for each person in the database. Inside that loop, I’ll once again use SCAN to retrieve information for each character. The code looks like this:

   method run();
      dcl int endloop;
      /* Make a GET to retrieve the number of persons */
      url='http://swapi.co/api/people';
      GetResponse(url);
      endloop=scan(Response,2,'".,:{}');
      /* Make sequential GETs, one for each person */
      do ID =1 to endloop;
         url=cats('http://swapi.co/api/people/',id);
         GetResponse(url);
         do;
            name=      scan(Response,2,'".,:{}');
            height=    scan(Response,4,'".,:{}');
            mass=      scan(Response,6,'".,:{}');
            hair_color=scan(Response,8,'".,:{}');
            skin_color=scan(Response,10,'".,:{}');
            eye_color= scan(Response,12,'".,:{}');
            birth_year=scan(Response,14,'".,:{}');
            gender=    scan(Response,16,'".,:{}');
            if name ne 'Not found' then output;
         end;
      end;
   end;

This does the job nicely! Here's a small sample from the CastOfCharacters dataset:

Data sample from Cast Of Characters data set
Figure 1

As usual, you can download a zip file containing the complete SAS program and a copy of this article from the link below. Until next time, may the SAS be with you!
Mark

Jedi_SAS_Tricks_DS2_and_APIs.zip

Tags data step Developers DS2 Jedi SAS trick sas programming sas training tips and tricks
Share

About Author

SAS Jedi

Principal Technical Training Consultant

Mark Jordan (a.k.a. SAS Jedi) grew up in northeast Brazil as the son of Baptist missionaries. After 20 years as a US Navy submariner pursuing his passion for programming as a hobby, in 1994 he retired, turned his hobby into a dream job, and has been a SAS programmer ever since. Mark writes and teaches a broad spectrum of SAS programming classes, and his book, "Mastering the SAS® DS2 Procedure: Advanced Data Wrangling Techniques" is in its second edition. When he isn’t writing, teaching, or posting “Jedi SAS Tricks”, Mark enjoys playing with his grand and great-grandchildren, hanging out at the beach, and reading science fiction novels. His secret obsession is flying toys – kites, rockets, drones – and though he usually tries to convince Lori that they are for the grandkids, she isn't buying it. Mark lives in historic Williamsburg, VA with his wife, Lori, and Stella, their cat. To connect with Mark, check out his SAS Press Author page, follow him on Twitter @SASJedi or connect on Facebook or LinkedIn.

11 Comments

  1. Pingback: Jedi SAS Tricks: Writing to Text Files from DS2 - SAS Learning Post

  2. Pingback: Using SAS DS2 to parse JSON - The SAS Dummy

  3. Richard Wright on

    So, OK, I'm at my wits end, here.

    I've got something of a lull and thought I'd take a little down time to learn DS2. And in doing so, stumbled onto your blog - actually through the tattoine blog - which brought up something else - later. Anyway ended up at the this article - GET the data you're looking for. And figured I'd just copy and paste it, and run. So that was a couple of hours ago,
    First, I get :ERROR: BASE driver, invalid PRIMARYPATH value, C:\Users\xxxxxxxxxxxx\My Documents\My SAS Files
    ERROR: BASE driver, cannot assign library/path
    ERROR: TKTS initialization failed
    And I've been going crazy trying to find out what this means.

    It looks like there's some kind of CONN= option in PROC DS2, but I sure can't find anything about it the PROC DS2 reference.

    So then I saw you got to this by reading the "section on the HTTP package". What section? I've been searching the online doc. and the web version, and I can't find anything about the HTTP package in DS2.

    Regarding the TATTOINE blog and what got me to here to begin with. It occurred to me that a real SAS Jedi master would send off the question to wolfram/alpha (https://www.wolframalpha.com/input/?i=how+many+meters+in+200+light-years). Which I tried using the FILENAME url method. And I got back the answer, sort of, but not readily understandable. I thought maybe DS2 had something that would help, but I can't even get elementary code to run. sigh.

    • SAS Jedi
      Mark Jordan on

      Were you using the complete SAS code from the ZIP file or did you copy and paste just the RUN() method from the second part and try to run that? If you copied the entire first PROC DS2 step, it should have run - it's a DATA _NULL_ step and shouldn't write output data.

      The "ERROR: BASE driver, invalid PRIMARYPATH value" message is usually caused by having a SAS LIBREF assigned which points to a non-existent SAS library location. Setting OPTIONS MSGLEVEL=I; will let you see the complete connect string that PROC DS2 is using. This will include connections to every LIBREF assigned in the SAS session, and might help you troubleshoot the problem with your LIBREF. A note of caution: connect strings are very sparsely documented at this stage.

      The documentation for the HTTP package is in the DS2 Language Reference section titled "DS2 HTTP Package Methods, Operators, and Statements"

      And finally, I didn't use FILENAME access in this blog because I was discussing DS2 techniques. The DS2 language is designed to access structured data (as indicated early in the blog posting "DS2 has no text file handling"), so FILENAME, INPUT and INFILE statements won't work in this context. Had I intended to use a base SAS data step to retrieve that information from the Internet, a Wolfram Alpah query like the one above isn't not the optimal approach, because it will return a complete HTML page with lots of extraneous information and html tags in it. It would be complicated to extract the actual answer from the noise. I would probably use the Wolfram API (which returns XML) to answer this type of question, as it would be easier to extract the desired information. This might be fodder for a future blog...

      Thanks for taking the time to provide feedback and for trying out new SAS techniques.

      Stay SASy, my friend!
      Mark

      • AKS on

        Hi Mark,

        Can you please let us know, how to write the put statement output from data _null_ procedure to a file in DS2 ?

        Thanks,
        AKS

        • SAS Jedi
          SAS Jedi on

          AKS,
          DS2 does not read or write to unstructured data (text files), only structured data (tables). To create table output from a DS2 DATA _NULL_ program, replace _NULL_ with the name of an output dataset. However, if you remove the _NULL_ specification altogether, the DS2 DATA program will return its result set directly to SAS ODS. You could use an ODS destination like ODS CSVALL to produce a CSV file from the DATA program results. Something like this ought to do the trick:

          data class; set sashelp.class; run;

          ods csvall file='my.csv';
          proc ds2;
          data;
          method run();
          set class;
          end;
          enddata;
          run;
          quit;
          ods csvall close;

          Hope this helps. May the SAS be with you!
          Mark

Back to Top