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.