Table of Contents

guest
2020-12-03
       SAS Client API Library
         SAS Setup
         SAS Macros
         SAS Demos

SAS Client API Library


The LabKey Client API Library for SAS makes it easy for SAS users to load live data from a LabKey Server into a native SAS dataset for analyis. It also enables SAS users to insert, update, and delete records stored on a LabKey Server.

Any access to read or modify data in SAS datasets is performed under the user's LabKey Server account profile, with all proper security enforced on the server. Users only have access to read and/or modify data in SAS that they are authorized to view or modify within LabKey Server. User credentials are obtained from a separate location than the running SAS program so that SAS programs can be shared without compromising security.

The SAS macros use the Java Client Library to send, receive, and process requests to the server. They provide functionality similar to the Rlabkey Package.

Topics

SAS Security

The SAS library performs all requests to the LabKey Server under a given user account with all the proper security enforced on the server. User credentials are obtained from a separate location than the running SAS program so that SAS programs may be shared without compromising security.

User credentials are read from the netrc file in the user’s home directory, so as to keep those credentials out of SAS programs, which may be shared between users.

Related Topics




SAS Setup


The LabKey/SAS client library is a set of SAS macros that retrieve data from an instance of LabKey Server as SAS data sets. The SAS macros use the Java Client Library to send, receive, and process requests to the server. This topic helps you set up SAS to work with LabKey.

Configure your SAS Installation to Use the SAS/LabKey Interface

The steps in this section illustrate the process; be sure to substitute your own current version numbers for the examples given.

  • Install SAS
  • Get the latest SAS client distribution, as follows:
    • Go to the topic API Resources, which displays a table of API resources.
    • In the row of SAS related links, click Distribution, which takes you to the Artifactory publication site.
    • On Artifactory, select the .zip file, for example labkey-api-sas-1.0.0.zip, and click Download.
  • Extract this file to a local directory (these instructions assume "c:\sas"). The directory should contain a number of .jar files (the Java client library and its dependencies) and 12 .sas files (the SAS macros).
  • Open your default SAS configuration file, sasv9.cfg. You will find it in your SAS installation in a location similar to C:\Program Files\SASHome\x86\SASFoundation\9.3\nls\en)
  • In the -SET SASAUTOS section, add the path to the SAS macros to the end of the list (e.g., "C:\sas")
  • Configure your Java Runtime Environment (JRE) based on your SAS version. For current information, check the SAS documentation. You may need to install security updates or make other adjustments as recommended there.
  • Near the top of sasv9.cfg, add a line like the following, including a path list surrounded by double quotes. The path list must use the correct separator for your operating system. For Windows, use a semicolon ";" and for Mac, us a colon ":"
    -set classpath "[full paths to all .jar files]"
Example Java classpath for Windows (use the actual version numbers from the package you unpacked):

-set classpath "C:\sas\commons-codec-1.6.jar;C:\sas\commons-logging-1.1.3.jar;C:\sas\fluent-hc-4.3.5.jar;C:\sas\httpclient-4.3.5.jar;C:\sas\httpclient-cache-4.3.5.jar;C:\sas\httpcore-4.3.2.jar;C:\sas\httpmime-4.3.5.jar;C:\sas\json_simple-1.1.jar;C:\sas\opencsv-2.0.jar;C:\sas\labkey-client-api-15.2.jar"

Example Java classpath for Mac (use the actual version numbers from the package you unpacked):

-set classpath "/sas/commons-codec-1.6.jar:/sas/commons-logging-1.1.3.jar:/sas/fluent-hc-4.3.5.jar:/sas/httpclient-4.3.5.jar:/sas/httpclient-cache-4.3.5.jar:/sas/httpcore-4.3.2.jar:/sas/httpmime-4.3.5.jar:/sas/json_simple-1.1.jar:/sas/opencsv-2.0.jar:/sas/labkey-client-api-15.2.jar"

Configure LabKey Server and Run the Test Script

  1. On your local version of LabKey Server, create a list called "People" in your home folder, inferring the structure and populating it using the file "demo.xls".
  2. Configure your .netrc or _netrc file in your home directory.
  3. Run SAS
  4. Execute "proc javainfo; run;" in a program editor; this command should display detailed information about the java environment in the log. Verify that java.version matches the JRE you set above.
  5. Load demo.sas
  6. Run it

Related Topics




SAS Macros


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 %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 "/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 "/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 "/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 "/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.

Related Topics




SAS Demos


This topic provides demos to get you started using the SAS client API.

Simple Demo

You can select (Export) > Script > SAS above most query views to export a script that selects the columns shown.

For example, performing this operation on the LabResults dataset in the example study, produces the following macro:

%labkeySelectRows(dsn=mydata,
baseUrl="https://www.labkey.org",
folderPath="/Explore/Research Study",
schemaName="study",
queryName="LabResults");

This SAS macro selects the rows shown in this custom grid into a dataset called 'mydata'.

Full SAS Demo

The sas-demo.zip archive attached to this page provides a SAS script and Excel data files. You can use these files to explore the selectRows, executeSql, insert, update, and delete operations of the SAS/LabKey Library.

Steps for setting up the demo:

  1. Make sure that you or your admin has Set Up SAS to use the SAS/LabKey Interface.
  2. Make sure that you or your admin has set up a .netrc file to provide you with appropriate permissions to insert/update/delete. For further information, see Create a netrc file.
  3. Download and unzip the demo files: sas-demo.zip. The zip folder contains a SAS demo script (demo.sas) and two data files (People.xls and MorePeople.xls). The spreadsheets contain demo data that goes with the script.
  4. Add the "Lists" web part to a folder on your LabKey Server if it has not yet been added.
  5. Create a new list called “People”, inferring the fields and importing the data by dragging and dropping the file People.xls into the list creation editor. Learn more in this topic: Create Lists.
  6. Create a second list called “MorePeople” using MorePeople.xls for inferring fields and importing data.
  7. Change the two references to baseUrl and folderPath in the demo.sas to match your server and folder.
  8. Run the demo.sas script in SAS.

Related Topics