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.

• The %labkeySetDefaults Macro
• The %labkeySelectRows Macro
• The %labkeyMakeFilter Macro
• The %labkeyExecuteSql Macro
• The %labkeyInsertRows, %labkeyUpdateRows and %labkeyDeleteRows Macros
• Quality Control Values

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:

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

%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:

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.

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

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:

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:

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'

Related Topics

• Create a netrc file
• SAS Setup
• SAS Client API Library