topic Re: Appending latest observation with PROC HTTP and JSON library in SAS Procedures

Appending latest observation with PROC HTTP and JSON library

DWhite — Tue, 27 Nov 2018 23:06:27 GMT

Hello. I am calling an API from a simple sensor reporting a couple of variables. The API presents the latest observation. I am able to successfully process the latest observation. I want to repeatedly call the API with a crontab to build a historical data set including the latest observation. The code below is how I am approaching this (for original source see: https://blogs.sas.com/content/sasdummy/2018/07/02/snackbot-api-timeseries/).

I am getting stuck on the append. I have tried Proc Append, and a DATA/SET statement (using below), but I am missing something. The "sensor_readings" table is always overwritten with the latest observation.

 
/* Call the API */
filename feeds temp;
proc http
  method="GET"
  url="https://api.thingspeak.com/channels/622255/feeds.json?results=2"
  out=feeds;
run;
 
/* JSON libname engine to read the result       */
libname sensor json fileref=feeds;
data sensor_readings;
  set sensor.feeds;
run;

Re: Appending latest observation with PROC HTTP and JSON library

kiranv_ — Tue, 27 Nov 2018 23:19:10 GMT

instead of data step can you try proc append. datastep overwrites your previous dataset with same name.

perm in below example is to use some permanent library which is available to you

proc append base= perm.sensor_readings  data=sensor.feeds;
run;

Re: Appending latest observation with PROC HTTP and JSON library

ChrisHemedinger — Wed, 28 Nov 2018 14:24:57 GMT

Sounds like a fun project! Because your API call works with a public site, I was able to try some things.

First, you might consider converting the values you read into proper SAS numerics -- will be easier to analyze trends, etc.

You can create a "template" data set for storing the records, and append this with either PROC APPEND or with DATA step SET statements -- taking care to add to, not simply replace, the existing data.

/* Call the API */
filename feeds temp;
proc http
  method="GET"
  url="https://api.thingspeak.com/channels/622255/feeds.json?results=2"
  out=feeds;
run;

/* assign this to a real permanent location */
libname perm (WORK); 

/* do this once to create a template for your data */
data perm.watt_sensor;
 length luminosity 8
        temperature 8
        created_at 8
        entry_id 8;
 format created_at datetime20.;
 stop;
run;

/* JSON libname engine to read the result       */
/* Prep just records with new readings          */
libname sensor json fileref=feeds;
data new_readings;
 set sensor.feeds (rename=(created_at=created_at_char));
 length luminosity 8
        temperature 8
        created_at 8
        entry_id 8;
  format created_at datetime20.;
  
  created_at = input(created_at_char,anydtdtm.);
  luminosity = input(compress(field1,'.','kd'),10.);
  temperature = input(compress(field2,'.','kd'),10.2);
  
  drop field: created_at_char ordinal:;
run;

/* or use PROC APPEND */
data perm.watt_sensor;
 set perm.watt_sensor 
     new_readings;
run;

Re: Appending latest observation with PROC HTTP and JSON library

DWhite — Thu, 29 Nov 2018 02:32:11 GMT

Kiranv_, The append did work. Thanks.

Re: Appending latest observation with PROC HTTP and JSON library

DWhite — Thu, 29 Nov 2018 02:40:38 GMT

Chris,

Thanks for the great code. I worked with my permanent libraries and eventually got everything worked out. I have been bouncing between SAS Studio and running it as crontab via a shell script, but it looks like I have the crontab working correctly. Cleaning up the JSON labels was my next step, so I appreciate you getting me there a lot faster.. I did end up using an Append statement, rather than the Data/Step. Enjoyed you snackbot post!

Dave