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:

NameTypeRequired?Description
baseUrlstringnThe 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"
folderPathstringnThe LabKey Server folder path in which to execute the request
schemaNamestringnThe name of the schema to query
queryNamestringnThe name of the query to request
userNamestringnThe 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.
passwordstringnThe user's password. See userName (above) for further details.
containerFilterstringnThis 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:

NameTypeRequired?Description
dsnstringyThe name of the SAS dataset to create and populate with the results
baseUrlstringyThe 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"
folderPathstringyThe LabKey Server folder path in which to execute the request
schemaNamestringyThe name of the schema to query
queryNamestringyThe name of the query to request
viewNamestringnThe name of a saved custom grid view of the given schema/query. If not supplied, the default grid will be returned.
filterstringnOne or more filter specifications created using the %makeFilter macro
columnsstringnA comma-delimited list of column name to request (if not supplied, the default set of columns are returned)
sortstringnA comma-delimited list of column names to sort by. Use a “-“ prefix to sort descending.
maxRowsnumbernIf set, this will limit the number of rows returned by the server.
rowOffsetnumbernIf 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.
showHidden1/0nBy 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.
userNamestringnThe user's login name. Please see the %labkeySetDefaults section for further details.
passwordstringnThe user's password. Please see the %labkeySetDefaults section for further details.
containerFilterstringnThis 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.

NameTypeRequired?Description
columnstringyThe column to filter upon
operatorstringyThe operator for the filter. See below for a list of acceptable operators.
valueanyyThe 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:

NameTypeRequired?Description
dsnstringyThe name of the SAS dataset to create and populate with the results
sqlstringyThe LabKey SQL to execute
baseUrlstringyThe 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"
folderPathstringyThe folder path in which to execute the request
schemaNamestringyThe name of the schema to query
maxRowsnumbernIf set, this will limit the number of rows returned by the server.
rowOffsetnumbernIf 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.
showHidden1/0nPlease see description in %labkeySelectRows.
userNamestringnThe user's login name. Please see the %labkeySetDefaults section for further details.
passwordstringnThe user's password. Please see the %labkeySetDefaults section for further details.
containerFilterstringnThis 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:

NameTypeRequired?Description
dsndatasetyA SAS dataset containing the rows to insert/update/delete
baseUrlstringyThe 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"
folderpathstringyThe folder path in which to execute the request
schemaNamestringyThe name of the schema
queryNamestringyThe name of the query within the schema
userNamestringnThe user's login name. Please see the %labkeySetDefaults section for further details.
passwordstringnThe 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.

discussion

Discussion

Search 

Pages 

previousnext
 
expand all collapse all