SAS/LabKey Library
The SAS/LabKey client library provides a set of SAS macros that retrieve data from an instance of LabKey Server as SAS data sets and allows modifications to LabKey Server data from within SAS. All requests to the LabKey Server are performed under the user's account profile, with all proper security enforced on the server.
The SAS macros use the Java Client Library to send, receive and process requests to the server. This page lists the SAS macros, parameters and usage examples.
Related topics:
The %labkeySetDefaults Macro
The %labkeySetDefaults macro sets connection information that can be used for subsequent requests. All parameters set via %labkeySetDefaults can be set once via %labkeySetDefaults, or passed individually to each macro.
The %labkeySetDefaults macro allows the SAS user to set the connection information once regardless of the number of calls made. This is convenient for developers, who can write more maintainable code by setting defaults once instead of repeatedly setting these parameters.
Subsequent calls to %labkeySetDefaults will change any defaults set with an earlier call to %labkeySetDefaults.
%labkeySetDefaults accepts the following parameters:
Name | Type | Required? | Description |
---|
baseUrl | string | n | The base URL for the target server. This includes the protocol (http, https) and the port number. It will also include the context path (commonly “/cpas” or “/labkey”), unless LabKey Server has been deployed as the root context. Example: "http://localhost:8080/labkey" |
folderPath | string | n | The LabKey Server folder path in which to execute the request |
schemaName | string | n | The name of the schema to query |
queryName | string | n | The name of the query to request |
userName | string | n | The user's login name. Note that the NetRC file includes both the userName and password. It is best to use the values stored there rather than passing these values in via a macro because the passwords will show up in the log files, producing a potential security hole. However, for chron jobs or other automated processes, it may be necessary to pass in userName and password via a macro parameter. |
password | string | n | The user's password. See userName (above) for further details. |
containerFilter | string | n | This parameter modifies how the query treats the folder. The possible settings are listed below. If not specified, "Current" is assumed. |
Options for the
containerFilter parameter:
- Current -- The current container
- CurrentAndSubfolders -- The current container and any folders it contains
- CurrentPlusProject -- The current container and the project folder containing it
- CurrentAndParents -- The current container and all of its parent containers
- CurrentPlusProjectAndShared -- The current container, its project folder and all shared folders
- AllFolders -- All folders to which the user has permission
Example usage of the %labkeySetDefaults macro:
%labkeySetDefaults(baseUrl="http://localhost:8080/labkey", folderPath="/home",
schemaName="lists", queryName="People");
The %labkeySelectRows Macro
The %labkeySelectRows macro allows you to select rows from any given schema and query name, optionally providing sorts, filters and a column list as separate parameters.
Parameters passed to an individual macro override the values set with %labkeySetDefaults.
Parameters are listed as required when they must be provided either as an argument to %labkeySelectRows or through a previous call to %labkeySetDefaults.
This macro accepts the following parameters:
Name | Type | Required? | Description |
---|
dsn | string | y | The name of the SAS dataset to create and populate with the results |
baseUrl | string | y | The base URL for the target server. This includes the protocol (http, https), the port number, and optionally the context path (commonly “/cpas” or “/labkey”). Example: "http://localhost:8080/labkey" |
folderPath | string | y | The LabKey Server folder path in which to execute the request |
schemaName | string | y | The name of the schema to query |
queryName | string | y | The name of the query to request |
viewName | string | n | The name of a saved custom grid view of the given schema/query. If not supplied, the default grid will be returned. |
filter | string | n | One or more filter specifications created using the %makeFilter macro |
columns | string | n | A comma-delimited list of column name to request (if not supplied, the default set of columns are returned) |
sort | string | n | A comma-delimited list of column names to sort by. Use a “-“ prefix to sort descending. |
maxRows | number | n | If set, this will limit the number of rows returned by the server. |
rowOffset | number | n | If set, this will cause the server to skip the first N rows of the results. This, combined with the maxRows parameter, enables developers to load portions of a dataset. |
showHidden | 1/0 | n | By default hidden columns are not included in the dataset, but the SAS user may pass 1 for this parameter to force their inclusion. Hidden columns are useful when the retrieved dataset will be used in a subsequent call to %labkeyUpdate or %labkeyDetele. |
userName | string | n | The user's login name. Please see the %labkeySetDefaults section for further details. |
password | string | n | The user's password. Please see the %labkeySetDefaults section for further details. |
containerFilter | string | n | This parameter modifies how the query treats the folder. The possible settings are listed in the %labkeySetDefaults macro section. If not specified, "Current" is assumed. |
Examples:The SAS code to load all rows from a list called "People" can define all parameters in one function call:
%labkeySelectRows(dsn=all, baseUrl="http://localhost:8080/labkey",
folderPath="/home", schemaName="lists", queryName="People");
Alternatively, default parameter values can be set first with a call to %labkeySetDefaults. This leaves default values in place for all subsequent macro invocations. The code below produces the same output as the code above:
%labkeySetDefaults(baseUrl="http://localhost:8080/labkey", folderPath="/home",
schemaName="lists", queryName="People");
%labkeySelectRows(dsn=all2);
This example demonstrates column list, column sort, row limitation, and row offset:
%labkeySelectRows(dsn=limitRows, columns="First, Last, Age",
sort="Last, -First", maxRows=3, rowOffset=1);
Further examples are available in the %labkeyMakeFilter section below.
The %labkeyMakeFilter Macro
The %labkeyMakeFilter macro constructs a simple compare filter for use in the %labkeySelectRows macro. It can take one or more filters, with the parameters listed in triples as the arguments. All operators except "MISSING and "NOT_MISSING" require a "value" parameter.
Name | Type | Required? | Description |
---|
column | string | y | The column to filter upon |
operator | string | y | The operator for the filter. See below for a list of acceptable operators. |
value | any | y | The value for the filter. Not used when the operator is "MISSING" or "NOT_MISSING". |
The operator may be one of the following:
- EQUAL
- NOT_EQUAL
- NOT_EQUAL_OR_MISSING
- DATE_EQUAL
- DATE_NOT_EQUAL
- MISSING
- NOT_MISSING
- GREATER_THAN
- GREATER_THAN_OR_EQUAL
- LESS_THAN
- LESS_THAN_OR_EQUAL
- CONTAINS
- DOES_NOT_CONTAIN
- STARTS_WITH
- DOES_NOT_START_WITH
- IN
- NOT_IN
- CONTAINS_ONE_OF
- CONTAINS_NONE_OF
Note: For simplicity and consistency with other client libraries, EQUALS_ONE_OF has been renamed IN and EQUALS_NONE_OF has been renamed NOT_IN. You may need to update your code to support these new filter names.
Examples:/* Specify two filters: only males less than a certain height. */
%labkeySelectRows(dsn=shortGuys, filter=%labkeyMakeFilter("Sex", "EQUAL", 1,
"Height", "LESS_THAN", 1.2));
proc print label data=shortGuys; run;
/* Demonstrate an IN filter: only people whose age is specified. */
%labkeySelectRows(dsn=lateThirties, filter=%labkeyMakeFilter("Age",
"IN", "36;37;38;39"));
proc print label data=lateThirties; run;
/* Specify a grid and a not missing filter. */
%labkeySelectRows(dsn=namesByAge, viewName="namesByAge",
filter=%labkeyMakeFilter("Age", "NOT_MISSING"));
proc print label data=namesByAge; run;
The %labkeyExecuteSql Macro
The %labkeyExecuteSql macro allows SAS users to execute arbitrary LabKey SQL, filling a SAS dataset with the results.
Required parameters must be provided either as an argument to %labkeyExecuteSql or via a previous call to %labkeySetDefaults.
This macro accepts the following parameters:
Name | Type | Required? | Description |
---|
dsn | string | y | The name of the SAS dataset to create and populate with the results |
sql | string | y | The LabKey SQL to execute |
baseUrl | string | y | The base URL for the target server. This includes the protocol (http, https), the port number, and optionally the context path (commonly “/cpas” or “/labkey”). Example: "http://localhost:8080/labkey" |
folderPath | string | y | The folder path in which to execute the request |
schemaName | string | y | The name of the schema to query |
maxRows | number | n | If set, this will limit the number of rows returned by the server. |
rowOffset | number | n | If set, this will cause the server to skip the first N rows of the results. This, combined with the maxrows parameter, enables developers to load portions of a dataset. |
showHidden | 1/0 | n | Please see description in %labkeySelectRows. |
userName | string | n | The user's login name. Please see the %labkeySetDefaults section for further details. |
password | string | n | The user's password. Please see the %labkeySetDefaults section for further details. |
containerFilter | string | n | This parameter modifies how the query treats the folder. The possible settings are listed in the %labkeySetDefaults macro section. If not specified, "Current" is assumed. |
Example:/* Set default parameter values to use in subsequent calls. */
%labkeySetDefaults(baseUrl="http://localhost:8080/labkey", folderPath="/home",
schemaName="lists", queryName="People");
/* Query using custom SQL… GROUP BY and aggregates in this case. */
%labkeyExecuteSql(dsn=groups, sql="SELECT People.Last, COUNT(People.First)
AS Number, AVG(People.Height) AS AverageHeight, AVG(People.Age)
AS AverageAge FROM People GROUP BY People.Last");
proc print label data=groups; run;
/* Demonstrate UNION between two different data sets. */
%labkeyExecuteSql(dsn=combined, sql="SELECT MorePeople.First, MorePeople.Last
FROM MorePeople UNION SELECT People.First, People.Last FROM People ORDER BY 2");
proc print label data=combined; run;
The %labkeyInsertRows, %labkeyUpdateRows and %labkeyDeleteRows Macros
The %labkeyInsertRows, %labkeyUpdateRows and %labkeyDeleteRows macros are all quite similar. They each take a SAS dataset, which may contain the data for one or more rows to insert/update/delete.
Required parameters must be provided either as an argument to %labkeyInsert/Update/DeleteRows or via a previous call to %labkeySetDefaults.
Parameters:
Name | Type | Required? | Description |
---|
dsn | dataset | y | A SAS dataset containing the rows to insert/update/delete |
baseUrl | string | y | The base URL for the target server. This includes the protocol (http, https), the port number, and optionally the context path (commonly “/cpas” or “/labkey”). Example: "http://localhost:8080/labkey" |
folderpath | string | y | The folder path in which to execute the request |
schemaName | string | y | The name of the schema |
queryName | string | y | The name of the query within the schema |
userName | string | n | The user's login name. Please see the %labkeySetDefaults section for further details. |
password | string | n | The user's password. Please see the %labkeySetDefaults section for further details. |
The key difference between the macros involves which columns are required for each case.
For insert, the input dataset should not include values for the primary key column (‘lsid’ for study datasets), as this will be automatically generated by the server.
For update, the input dataset must include values for the primary key column so that the server knows which row to update. The primary key value for each row is returned by %labkeySelectRows and %labkeyExecuteSql if the ‘showHidden’ parameter is set to 1.
For delete, the input dataset needs to include only the primary key column. It may contain other columns, but they will be ignored by the server.
Example: The following code inserts new rows into a study dataset:
/* Set default parameter values to use in subsequent calls. */
%labkeySetDefaults(baseUrl="http://localhost:8080/labkey", folderPath="/home",
schemaName="lists", queryName="People");
data children;
input First : $25. Last : $25. Appearance : mmddyy10. Age Sex Height ;
format Appearance DATE9.;
datalines;
Pebbles Flintstone 022263 1 2 .5
Bamm-Bamm Rubble 100163 1 1 .6
;
/* Insert the rows defined in the children data set into the "People" list. */
%labkeyInsertRows(dsn=children);
Quality Control Values
The SAS library accepts special values in datasets as indicators of the quality control status of data. The QC values currently available are:
- 'Q': Data currently under quality control review
- 'N': Required field marked by site as 'data not available'
The SAS library will save these as “special missing values” in the data set.