How to filter records from SAS using CURL and SAS9API

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.