![]() | SUMMARY | DETAIL |
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.
Macro Detail |
%lsaf_getquerycolumns(lsaf_querytype=, sas_dsname=work.lsafgetquerycolumns);
[ Exposure:
external
]
_lsafMsg_
-
The return message._lsafRC_
-
The return code.lsaf_querytype
-
- Required - The case-insensitive query type of the column. Valid values:sas_dsname
-
- Optional - The name of the output SAS data set to contain the metadata
%lsaf_getquerytemplatedataset(sas_dsname=work.lsafgetquerytemplatedataset);
[ Exposure:
external
]
_lsafMsg_
-
The return message._lsafRC_
-
The return code.sas_dsname
-
- Optional - The name of the output SAS data set, specified as libref.dataset.
%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
]
_lsafAuditDetailsExportPath_
-
The full path to the query results .csv file, which includes_lsafMsg_
-
The return message._lsafRC_
-
The return code.lsaf_querydataset
-
- Required - The name of the input SAS data set that contains the querylsaf_validateddataset
-
- Required - The name of the output SAS data set to contain thelsaf_exportlocation
-
- Required - The case-insensitive output location for the exported file.lsaf_exportpath
-
- Required - The case-sensitive output path for the exported CSV file.lsaf_overwrite
-
- Optional - Indicates whether an existing nonversioned file is overwritten.lsaf_enableversioning
-
- Optional - Indicates whether versioning is enabled for a new filelsaf_versiontype
-
- Optional - The version type to use to create a file in the repository. Whenlsaf_customversion
-
- Conditional - The version number to use to create a customized versionedlsaf_comment
-
- Optional - The check-in comment to associate with the action of adding a file
%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
]
_lsafAuditDetailsExportPath_
-
The full path to the query results .csv file, which includes_lsafMsg_
-
The return message._lsafRC_
-
The return code.lsaf_querydataset
-
- Required - The name of the input SAS data set that contains the querylsaf_validateddataset
-
- Required - The name of the output SAS data set to contain thelsaf_exportlocation
-
- Required - The case-insensitive output location for the exported file.lsaf_exportpath
-
- Required - The case-sensitive output path for the exported CSV file.lsaf_overwrite
-
- Optional - Indicates whether an existing nonversioned file is overwritten.lsaf_enableversioning
-
- Optional - Indicates whether versioning is enabled for a new filelsaf_versiontype
-
- Optional - The version type to use to create a file in the repository. Whenlsaf_customversion
-
- Conditional - The version number to use to create a customized versionedlsaf_comment
-
- Optional - The check-in comment to associate with the action of adding a file