How to filter records from SAS using CURL and SAS9API

  • Date September 13, 2019
  • Written by Vasilij Nevlev
  • Category Curl

This guide shows how to extract a specific set of records from a SAS dataset using SAS9API and CURL.

Introduction

For this example you need:

  • Access to a working and configured instance of SAS9API.
  • Windows/Unix command-line interface with CURL installed.

In this example, we use the SASHELP.CLASS dataset which is available on the majority of SAS platforms and specify the following parameters:

Parameter Description
0 ServerName Your server name
1 LibraryName Created library name, as will be used in LIBNAME statement
2 RepositoryName Repository name
3 DatasetName Your dataset name
4 Offset Dataset record offset
5 Limit Number of records to retrieve (max value is 10000)
6 Filter Dataset filter in JSON format (example: {“Sex”: “M”, “Age”: 14}). Must be URL-encoded

 

Step 1 – Get all the records.

To get all 19 records of the dataset, we can simply send a GET request to the following endpoint:

/sas/libraries/{{LibraryName}}/datasets/{{DatasetName}}/data?serverUrl={{ServerURI}}&serverPort={{ServerPort}}

We to run the following CURL command:

curl -X GET --header 'Accept:application/xml' 'http://yourServer/sasrestapi//sas/libraries/SASHELP/datasets/CLASS/data?serverUrl=yourserverURI&serverPort=yourPort'

The response is:

{
   "status": 200,
   "error": null,
   "payload": [
       {
           "Age": 14.0,
           "Height": 69.0,
           "Name": "Alfred",
           "Sex": "M",
           "Weight": 112.5
       },
       {
           "Age": 13.0,
           "Height": 56.5,
           "Name": "Alice",
           "Sex": "F",
           "Weight": 84.0
       },
…

Status 200 – means that the request was fulfilled without errors and all the records from the SASHELP.CLASS dataset were retreived.

Step 2 – Get all the records using offset and limit options.

You can retrieve data from the dataset by a dataset name, and a library name by using the offset and limit options. In the example below limit=10 and offset=7 which means that you will receive no more than 10 records starting from the 8th record.

/sas/servers/{{workspace server name}}/libraries/{{default library}} /datasets/{{default dataset}} /data?repositoryName=Name&limit=10&offset=7

Our CURL request looks like this:

curl -X GET --header 'Accept:application/xml' 'http://yourServer/sasrestapi//sas/servers/workspaceServer/libraries/SASHELP/datasets/CLASS/data?repositoryName=Name&limit=10&offset=7'

The limit and offset parameters allow you to get a data segment with data of a certain size.

{
   "status": 200,
   "error": null,
   "payload": [
       {
           "Age": 15.0,
           "Height": 62.5,
           "Name": "Janet",
           "Sex": "F",
           "Weight": 112.5
       },
       {
           "Age": 13.0,
           "Height": 62.5,
           "Name": "Jeffrey",
           "Sex": "M",
           "Weight": 84.0   
}
...

Ten records starting from the 8th record will be retrieved as the result of this request. It means that there are seven more records in the dataset which were not retrieved.

Step 3 – Applying filters.

You can use filters to extract the data:

/sas/servers/{{workspace server name}}/libraries/{{library}} /datasets/{{dataset}} /data?repositoryName=Name&limit=100&offset=1&filter= {"Sex": "F", "Age": 14}

We use the following CURL command to get all the records with Sex=”F” and Age=14:

curl -X GET --header 'Accept:application/xml' 'http://yourServer/sasrestapi//sas/servers/workspaceserver/libraries/SASHELP/datasets/CLASS/data?repositoryName=Name&limit=100&offset=1&filter=%20%7B%22Sex%22%3A%20%22F%22%2C%20%22Age%22%3A%2014%7D'

The response:

{
   "status": 200,
   "error": null,
   "payload": [
       {
           "Age": 14.0,
           "Height": 64.3,
           "Name": "Judy",
           "Sex": "F",
           "Weight": 90.0
       }
   ]
 }

There is only one 14-year old girl – Judy.

Conclusion

This example shows how to retrieve data from a SAS dataset knowing its name, a library name and a server name and the name of the library using the offset and limit parameters and how to use filters for extracting the data.

For this and other examples, see www.sas9api.io/examples.