Checks & Stores an input filter table and returns the Filter Key. More...

Detailed Description

Used to generate a FILTER_RK from an input query dataset. This process requires several permanent tables (names are configurable). The benefit of storing query values at backend is to enable stored 'views' of filtered tables at frontend (ie, when building SAS-Powered Apps). This macro is also used in Data Controller for SAS.

A more recent feature of this macro is the ability to support filter queries on Format Catalogs. This is achieved by adding a -FC suffix to the libds parameter - where the "ds" in this case is the catalog name.

Parameters

[in] libds= The target dataset to be filtered (lib should be assigned). If filtering a format catalog, add the following suffix: -FC.

[in]

queryds=

(WORK.FILTERQUERY) The temporary input query dataset to be validated. Has the following format:

GROUP_LOGIC:$3	SUBGROUP_LOGIC:$3	SUBGROUP_ID:8.	VARIABLE_NM:$32	OPERATOR_NM:$10	RAW_VALUE:$32767
AND	AND	1	SOME_BESTNUM	>	1
AND	AND	1	SOME_TIME	=	77333

[in]

filter_summary=

(PERM.FILTER_SUMMARY) Permanent table containing summary filter values. The definition is available by running mp_coretable.sas as follows: mp_coretable(FILTER_SUMMARY). Example values:

FILTER_RK:best.	FILTER_HASH:$32.	FILTER_TABLE:$41.	PROCESSED_DTTM:datetime19.
`1`	`540E96F566D194AB58DD4C413C99C9DB`	`VIYA6014.MPE_TABLES`	`1956084246`
`2`	`87737DB9EEE2650F5C89956CEAD0A14F`	`VIYA6014.MPE_X_TEST`	`1956084452.1`
`3`	`8048BD908DBBD83D013560734E90D394`	`VIYA6014.MPE_TABLES`	`1956093620.6`

[in]

filter_detail=

(PERM.FILTER_DETAIL) Permanent table containing detailed (raw) filter values. The definition is available by running mp_coretable.sas as follows: mp_coretable(FILTER_DETAIL). Example values:

FILTER_HASH:$32.	FILTER_LINE:best.	GROUP_LOGIC:$3.	SUBGROUP_LOGIC:$3.	SUBGROUP_ID:best.	VARIABLE_NM:$32.	OPERATOR_NM:$12.	RAW_VALUE:$4000.	PROCESSED_DTTM:datetime19.
`540E96F566D194AB58DD4C413C99C9DB`	`1`	`AND`	`AND`	`1`	`LIBREF`	`CONTAINS`	`DC`	`1956084245.8`
`540E96F566D194AB58DD4C413C99C9DB`	`2`	`AND`	`OR`	`2`	`DSN`	`=`	`MPE_LOCK_ANYTABLE`	`1956084245.8`
`87737DB9EEE2650F5C89956CEAD0A14F`	`1`	`AND`	`AND`	`1`	`PRIMARY_KEY_FIELD`	`IN`	`(1,2,3)`	`1956084451.9`

[in] lock_table= (PERM.LOCK_TABLE) Permanent locking table. Used to manage concurrent access. The definition is available by running mp_coretable.sas as follows: mp_coretable(LOCKTABLE).

[in] maxkeytable= (0) Optional permanent reference table used for retained key tracking. Described in mp_retainedkey.sas.

[in] mdebug= (1) set to 1 to enable DEBUG messages

[out] outresult= (work.result) The result table with the FILTER_RK

[out] outquery= (work.query) The original query, taken as extract after table load

SAS Macros

Related Macros

Version: 9.2

Author: Allan Bowe

Definition in file mp_filterstore.sas.