BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DWhite
Obsidian | Level 7

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

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;

 

View solution in original post

4 REPLIES 4
kiranv_
Rhodochrosite | Level 12

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;

 

DWhite
Obsidian | Level 7

Kiranv_, The append did work. Thanks. 

ChrisHemedinger
Community Manager

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;

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.
DWhite
Obsidian | Level 7

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

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1368 views
  • 3 likes
  • 3 in conversation