Module QueryService

Macros to help construct and query data within the SAS Life Science Analytics Framework.

Query macros extract data based on a query built from an input data set. The extracted data is stored in a .csv file in the workspace or repository.

The input data set is validated prior to executing the query. The results of that validation are returned to the user in an output data set.

If the input data set validation succeeds with no errors, the resulting query is printed to the log.

Information concerning the necessary columnClass and columnName data, as well as their data types, can be retrieved by running the macro lsaf_getquerycolumns.

The user must have the proper privilege to execute the query, the required privilege(s) for each query macro are listed in the macro details below.

If the query results in more than one million records, the macro will fail.

See the LSAF Macros API Users Guide for examples of performing various queries.

Global macro variables specific to each query contain the full path of the query results that are provided in a comma-separated file, including the file extension. If the query is not executed or if the macro processing results in a failure, the macro variable is blank.

The query macros share the same parameters, following is a more detailed explanation of each.

%LSAF_QUERY<QUERY NAME>(lsaf_querydataset=, lsaf_validateddataset=work.lsafqueryauditentries, lsaf_exportlocation=, lsaf_exportpath=, lsaf_overwrite=0, lsaf_enableversioning=0, lsaf_versiontype=MAJOR, lsaf_customversion=, lsaf_comment=);

PARAMETER: lsaf_querydataset - The name of the input SAS data set containing the query metadata provided as libref.dataset. The data set must contain at a minimum the following columns. All other columns are ignored.

COLUMN1: recordType - The type of query element the record represents.

VALID VALUES: SELECT - Indicates that the supplied column will be included in the resulting file. If no records with recordType=SELECTED are provided, all allowable columns will be included in the query.
VALID VALUES: ORDER_ASCENDING - Indicates that the resulting file will be sorted by the supplied column in ascending order with the level indicated by the specified value.
VALID VALUES: ORDER_DESCENDING - Indicates that the resulting file will be sorted by the supplied column in descending order with the level indicated by the specified value.
VALID VALUES: CONSTRAINT - Indicates data to be used to create a single comparison condition for the query.
VALID VALUES: CONSTRAINT_RANGE - Indicates data to be used to create a single comparison to determine if a column value falls between the two provided values. This option is available only for query columns of type numeric and date.
VALID VALUES: LOGICAL_OPERATOR - Indicates data to be used to join the query. The data set does not have to include a record with recordType=LOGICAL_OPERATOR if there is only one query constraint represented in the data set.

COLUMN2: columnClass - The class of the column to be used with the query operation associated with the record type. ColumnClass is not required when recordType is LOGICAL_OPERATOR and will be ignored if specified.

COLUMN3: columnName - The name of the column to be used with the query operation associated with the record type. ColumnName is not required when recordType is LOGICAL_OPERATOR and will be ignored if specified.

COLUMN4: value - The value to be used with the query operation associated with the record type.

If recordType is SELECT, a value is not required and will be ignored if specified.
If recordType is ORDER_ASCENDING or ORDER_DESCENDING, the value indicates the sort level for the specified columns. The value is required, must be an integer, and must be unique among the ORDERx records in the data set. They do not need to be sequential or start at 1. The ORDERx records will be sorted by value prior to processing.
If recordType is CONSTRAINT, a value is required and must match the expected type of the indicated column (e.g. date, numeric).
If recordType is CONSTRAINT_RANGE, the value is expected to be two comma-separated values of like types (e.g. date) where the first value precedes the second and the type is consistent with the type of the specified column. Example: 04Jan2018:20:48:41, 19Jan2019:40:00:00.
If recordType is LOGICAL_OPERATOR, the value must indicate how the constraint records will be joined. Valid values are case insensitive: AND, OR.

COLUMN5: comparator - Indicates how the query should handle the constraint. Comparator is required when recordType is CONSTRAINT. It will be ignored if supplied for any other record type.

VALID VALUES: EQUAL, NOT_EQUAL, LESS_THAN, GREATER_THAN, LESS_THAN_OR_EQUAL, GREATER_THAN_OR_EQUAL, LIKE, NOT_LIKE.

COLUMN6: isCaseSensitive - A boolean value signifying if a column value is treated as case-sensitive. Case-sensitivity is only applicable when recordType is ORDER_ASCENDING, ORDER_DESCENDING, or CONSTRAINT and the associated column is of type STRING. Otherwise, the value, if supplied, is ignored. If a value is not supplied for applicable record/column types, the default value is applied.

VALID VALUES: 0 = No
VALID VALUES: 1 = Yes (Default)

PARAMETER: lsaf_validateddataset - The name of the output SAS data set reporting the results of the validation of the records from the input data set lsaf_querydataset provided as libref.dataset. The default data set name varies with each macro (see details below). The data set contains all the columns and unaltered row data from the lsaf_querydataset plus additional columns with the following names.

COLUMN1: columnType - Indicates the data type of the associated column. This value is blank when recordType is LOGICAL_OPERATOR.
COLUMN2: validationNote - A note explaining why the validation failed or blank if no error was found.

PARAMETER: lsaf_exportlocation - The case-insensitive output location for the exported file. Valid values: WORKPSACE or REPOSITORY.

PARAMETER: lsaf_exportpath - The case-sensitive output path for the exported CSV file.

PARAMETER: lsaf_overwrite - Indicates whether existing nonversioned files are overwritten.

VALID VALUES: 0 = No (Default)
VALID VALUES: 1 = Yes.

PARAMETER: lsaf_enableversioning - Ignored when lsaf_exportlocation=WORKSPACE. Otherwise, indicates whether versioning is enabled for a new exported file in the repository.

VALID VALUES: 0 = No (Default)
VALID VALUES: 1 = Yes.

PARAMETER: lsaf_versiontype - Ignored when lsaf_exportlocation is WORKSPACE or lsaf_enableversioning is FALSE. Otherwise, indicates the version type to use to create a file in the REPOSITORY.

VALID VALUES: MAJOR (Default), MINOR, or CUSTOM.

PARAMETER: lsaf_customversion - Ignored when lsaf_exportlocation is WORKSPACE, or lsaf_enableversioning is FALSE, or lsaf_versiontype is not CUSTOM. Otherwise, indicates the version number to use to create a customized versioned file.

PARAMETER: lsaf_comment - Ignored when lsaf_exportlocation=WORKSPACE. Otherwise, indicates the check-in comment to associate with the action of adding the file to the repository.

Since: 2.3

Macro Summary
external

LSAF
%lsaf_getquerycolumns(lsaf_querytype=, sas_dsname=work.lsafgetquerycolumns);
Gets the metadata for the columns that are applicable for the specified query type. ...
external

LSAF
%lsaf_getquerytemplatedataset(sas_dsname=work.lsafgetquerytemplatedataset);
Gets the structure that is required as input to the macros that query the system. The metadata is ...
external

LSAF
%lsaf_queryauditdetails(lsaf_querydataset=, lsaf_validateddataset=work.lsafqueryauditdetails, lsaf_exportlocation=, lsaf_exportpath=, lsaf_overwrite=0, lsaf_enableversioning=0, lsaf_versiontype=MAJOR, lsaf_customversion=, lsaf_comment=);
Extracts detailed data from entries that are in the audit history records. The detailed data to extract ...
external

LSAF
%lsaf_queryauditentries(lsaf_querydataset=, lsaf_validateddataset=work.lsafqueryauditentries, lsaf_exportlocation=, lsaf_exportpath=, lsaf_overwrite=0, lsaf_enableversioning=0, lsaf_versiontype=MAJOR, lsaf_customversion=, lsaf_comment=);
Extracts audit entries from the audit history records. The entries to extract are specified in a query ...

Macro Detail

%lsaf_getquerycolumns(lsaf_querytype=, sas_dsname=work.lsafgetquerycolumns);

[ Exposure: external ]
Gets the metadata for the columns that are applicable for the specified query type.
The metadata is stored in a SAS data set.

Syntax:
%LSAF_GETQUERYCOLUMNS(LSAF_QUERYTYPE=lsaf-querytype <,SAS_DSNAME=SAS-data-set>);

          
History:
   2020-02-25
initial coding
Expected Macro Variables:
_lsafMsg_ - The return message.
_lsafRC_ - The return code.
Parameters:
lsaf_querytype - - Required - The case-insensitive query type of the column. Valid values:
        - AuditEntryQuery: The audit entry columns.
        - AuditEntryDetailQuery: The audit detail columns.
sas_dsname - - Optional - The name of the output SAS data set to contain the metadata
        for all of the query columns, specified as libref.dataset. The default value is
        WORK.LSAFGETQUERYCOLUMNS. The data set contains a row for each query column and
        columns with the following names:
        - queryType: The query type of the column.
        - columnQueryClass: The query class of the column.
        - columnName: The name of the column.
        - columnType: The type of the column.
File: lsaf_getquerycolumns.sas
First available in version: 2.3

%lsaf_getquerytemplatedataset(sas_dsname=work.lsafgetquerytemplatedataset);

[ Exposure: external ]
Gets the structure that is required as input to the macros that query the system. The metadata is
stored in a SAS data set with zero observations.

Syntax:
%LSAF_GETQUERYTEMPLATEDATASET(<SAS_DSNAME=SAS-data-set>);

          
History:
   2020-03-24
initial coding
Expected Macro Variables:
_lsafMsg_ - The return message.
_lsafRC_ - The return code.
Parameters:
sas_dsname - - Optional - The name of the output SAS data set, specified as libref.dataset.
        The default value is WORK.LSAFGETQUERYTEMPLATEDATASET. The data set contains no rows,
        only the following columns that are required to submit a query:
        - recordType: The type of query element that the record represents.
        - columnClass: The class of the column to use with the query operation that is
        associated with the record type.
        - columnName: The name of the column to use with the query operation that is
        associated with the record type.
        - value: The value to use with the query operation that is associated with the record
        type.
        - comparator: Indicates the operator that the query uses to handle the constraint.
        This value is required when recordType is CONSTRAINT.
        - isCaseSensitive: The Boolean value that indicates whether a column value is treated
        as case-sensitive.
File: lsaf_getquerytemplatedataset.sas
First available in version: 2.3

%lsaf_queryauditdetails(lsaf_querydataset=, lsaf_validateddataset=work.lsafqueryauditdetails, lsaf_exportlocation=, lsaf_exportpath=, lsaf_overwrite=0, lsaf_enableversioning=0, lsaf_versiontype=MAJOR, lsaf_customversion=, lsaf_comment=);

[ Exposure: external ]
Extracts detailed data from entries that are in the audit history records. The detailed data to extract
is specified in a query that is contained in a data set. The extracted data is stored in a
comma-separated values file that is located in your workspace or in the repository. Each detail is in a
separate row that includes the parent audit entry information.

Information that concerns the necessary columnClass and columnName data, as well as their data types,
can be retrieved by calling the macro lsaf_getquerycolumns with the type AuditEntryDetailQuery.

You must have the View Audit History privilege to execute the query.

See the SAS Life Science Analytics Framework Macros API Users Guide for examples and the Module
QueryService header text above for a detailed explanation of the macro parameters.

Syntax:
%LSAF_QUERYAUDITDETAILS(LSAF_QUERYDATASET=lsaf-querydataset,
LSAF_VALIDATEDDATASET=lsaf-validateddataset,
LSAF_EXPORTLOCATION=lsaf-exportlocation,
LSAF_EXPORTPATH=lsaf-exportpath,
LSAF_OVERWRITE=lsaf-overwrite,
LSAF_ENABLEVERSIONING=lsaf-enableversioning,
LSAF_VERSIONTYPE=lsaf-versiontype,
LSAF_CUSTOMVERSION=lsaf-customversion,
LSAF_COMMENT=lsaf-comment);

          
History:
   2020-03-30
initial coding
Expected Macro Variables:
_lsafAuditDetailsExportPath_ - The full path to the query results .csv file, which includes
        the file extension. If the query is not executed or if the macro processing fails,
        _lsafAuditDetailsExportPath_ is blank.
_lsafMsg_ - The return message.
_lsafRC_ - The return code.
Parameters:
lsaf_querydataset - - Required - The name of the input SAS data set that contains the query
        columnClass of AuditEntry, the valid values are USER, SYSTEM, and ADMIN.
lsaf_validateddataset - - Required - The name of the output SAS data set to contain the
        results of the validation of the records from the input data set lsaf_querydataset,
        specified as libref.dataset. The default data set name is WORK.LSAFQUERYAUDITENTRIES.
lsaf_exportlocation - - Required - The case-insensitive output location for the exported file.
        Valid values: REPOSITORY and WORKSPACE.
lsaf_exportpath - - Required - The case-sensitive output path for the exported CSV file.
lsaf_overwrite - - Optional - Indicates whether an existing nonversioned file is overwritten.
        Valid values: 0 (Default, No) and 1 (Yes).
lsaf_enableversioning - - Optional - Indicates whether versioning is enabled for a new file
        in the repository. When lsaf_exportlocation=WORKSPACE, this value is ignored.
        Valid values: 0 (Default, No) and 1 (Yes).
lsaf_versiontype - - Optional - The version type to use to create a file in the repository. When
        lsaf_exportlocation is WORKSPACE or lsaf_enableversioning is FALSE, this value is ignored.
        Valid values: MAJOR (Default), MINOR, and CUSTOM.
lsaf_customversion - - Conditional - The version number to use to create a customized versioned
        file in the repository. When lsaf_exportlocation is WORKSPACE, lsaf_enableversioning is
        FALSE, or lsaf_versiontype is not CUSTOM, this value is ignored.
lsaf_comment - - Optional - The check-in comment to associate with the action of adding a file
        to the repository. When lsaf_exportlocation=WORKSPACE, this value is ignored.
File: lsaf_queryauditdetails.sas
First available in version: 2.3

%lsaf_queryauditentries(lsaf_querydataset=, lsaf_validateddataset=work.lsafqueryauditentries, lsaf_exportlocation=, lsaf_exportpath=, lsaf_overwrite=0, lsaf_enableversioning=0, lsaf_versiontype=MAJOR, lsaf_customversion=, lsaf_comment=);

[ Exposure: external ]
Extracts audit entries from the audit history records. The entries to extract are specified in a query
that is contained in a data set. The extracted data is stored in a comma-separated values file that
is located in your workspace or in the repository.

Information that concerns the necessary columnClass and columnName data, as well as their data types,
can be retrieved by calling the macro lsaf_getquerycolumns with the type AuditEntryQuery.

You must have the View Audit History privilege to execute the query.

See the SAS Life Science Analytics Framework Macros API Users Guide for examples and the Module
QueryService header text above for a detailed explanation of the macro parameters.

Syntax:
%LSAF_QUERYAUDITENTRIES(LSAF_QUERYDATASET=lsaf-querydataset,
LSAF_VALIDATEDDATASET=lsaf-validateddataset,
LSAF_EXPORTLOCATION=lsaf-exportlocation,
LSAF_EXPORTPATH=lsaf-exportpath,
LSAF_OVERWRITE=lsaf-overwrite,
LSAF_ENABLEVERSIONING=lsaf-enableversioning,
LSAF_VERSIONTYPE=lsaf-versiontype,
LSAF_CUSTOMVERSION=lsaf-customversion,
LSAF_COMMENT=lsaf-comment);

          
History:
   2020-03-03
initial coding
Expected Macro Variables:
_lsafAuditDetailsExportPath_ - The full path to the query results .csv file, which includes
        the file extension. If the query is not executed or if the macro processing fails,
        _lsafAuditDetailsExportPath_ is blank.
_lsafMsg_ - The return message.
_lsafRC_ - The return code.
Parameters:
lsaf_querydataset - - Required - The name of the input SAS data set that contains the query
        columnClass of AuditEntry, the valid values are USER, SYSTEM, and ADMIN.
lsaf_validateddataset - - Required - The name of the output SAS data set to contain the
        results of the validation of the records from the input data set lsaf_querydataset,
        specified as libref.dataset. The default data set name is WORK.LSAFQUERYAUDITENTRIES.
lsaf_exportlocation - - Required - The case-insensitive output location for the exported file.
        Valid values: REPOSITORY and WORKSPACE.
lsaf_exportpath - - Required - The case-sensitive output path for the exported CSV file.
lsaf_overwrite - - Optional - Indicates whether an existing nonversioned file is overwritten.
        Valid values: 0 (Default, No) and 1 (Yes).
lsaf_enableversioning - - Optional - Indicates whether versioning is enabled for a new file
        in the repository. When lsaf_exportlocation=WORKSPACE, this value is ignored.
        Valid values: 0 (Default, No) and 1 (Yes).
lsaf_versiontype - - Optional - The version type to use to create a file in the repository. When
        lsaf_exportlocation is WORKSPACE or lsaf_enableversioning is FALSE, this value is ignored.
        Valid values: MAJOR (Default), MINOR, and CUSTOM.
lsaf_customversion - - Conditional - The version number to use to create a customized versioned
        file in the repository. When lsaf_exportlocation is WORKSPACE, lsaf_enableversioning is
        FALSE, or lsaf_versiontype is not CUSTOM, this value is ignored.
lsaf_comment - - Optional - The check-in comment to associate with the action of adding a file
        to the repository. When lsaf_exportlocation=WORKSPACE, this value is ignored.
File: lsaf_queryauditentries.sas
First available in version: 2.3