Macros for SAS Application Developers
https://github.com/sasjs/core
mp_filterstore.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Checks & Stores an input filter table and returns the Filter Key
4 @details Used to generate a FILTER_RK from an input query dataset. This
5 process requires several permanent tables (names are configurable). The
6 benefit of storing query values at backend is to enable stored 'views' of
7 filtered tables at frontend (ie, when building [SAS-Powered Apps](
8 https://sasapps.io)). This macro is also used in [Data Controller for SAS](
9 https://datacontroller.io).
10
11 A more recent feature of this macro is the ability to support filter queries
12 on Format Catalogs. This is achieved by adding a `-FC` suffix to the `libds`
13 parameter - where the "ds" in this case is the catalog name.
14
15
16 @param [in] libds= The target dataset to be filtered (lib should be assigned).
17 If filtering a format catalog, add the following suffix: `-FC`.
18 @param [in] queryds= (WORK.FILTERQUERY) The temporary input query dataset to
19 be validated. Has the following format:
20|GROUP_LOGIC:$3|SUBGROUP_LOGIC:$3|SUBGROUP_ID:8.|VARIABLE_NM:$32|OPERATOR_NM:$10|RAW_VALUE:$32767|
21|---|---|---|---|---|---|
22|AND|AND|1|SOME_BESTNUM|>|1|
23|AND|AND|1|SOME_TIME|=|77333|
24 @param [in] filter_summary= (PERM.FILTER_SUMMARY) Permanent table containing
25 summary filter values. The definition is available by running
26 mp_coretable.sas as follows: `mp_coretable(FILTER_SUMMARY)`. Example
27 values:
28|FILTER_RK:best.|FILTER_HASH:$32.|FILTER_TABLE:$41.|PROCESSED_DTTM:datetime19.|
29|---|---|---|---|
30|`1 `|`540E96F566D194AB58DD4C413C99C9DB `|`VIYA6014.MPE_TABLES `|`1956084246 `|
31|`2 `|`87737DB9EEE2650F5C89956CEAD0A14F `|`VIYA6014.MPE_X_TEST `|`1956084452.1`|
32|`3 `|`8048BD908DBBD83D013560734E90D394 `|`VIYA6014.MPE_TABLES `|`1956093620.6`|
33 @param [in] filter_detail= (PERM.FILTER_DETAIL) Permanent table containing
34 detailed (raw) filter values. The definition is available by running
35 mp_coretable.sas as follows: `mp_coretable(FILTER_DETAIL)`. Example
36 values:
37|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.|
38|---|---|---|---|---|---|---|---|---|
39|`540E96F566D194AB58DD4C413C99C9DB `|`1 `|`AND `|`AND `|`1 `|`LIBREF `|`CONTAINS `|`DC`|`1956084245.8 `|
40|`540E96F566D194AB58DD4C413C99C9DB `|`2 `|`AND `|`OR `|`2 `|`DSN `|`= `|` MPE_LOCK_ANYTABLE `|`1956084245.8 `|
41|`87737DB9EEE2650F5C89956CEAD0A14F `|`1 `|`AND `|`AND `|`1 `|`PRIMARY_KEY_FIELD `|`IN `|`(1,2,3) `|`1956084451.9 `|
42 @param [in] lock_table= (PERM.LOCK_TABLE) Permanent locking table. Used to
43 manage concurrent access. The definition is available by running
44 mp_coretable.sas as follows: `mp_coretable(LOCKTABLE)`.
45 @param [in] maxkeytable= (0) Optional permanent reference table used for
46 retained key tracking. Described in mp_retainedkey.sas.
47 @param [in] mdebug= set to 1 to enable DEBUG messages
48 @param [out] outresult= The result table with the FILTER_RK
49 @param [out] outquery= The original query, taken as extract after table load
50
51
52 <h4> SAS Macros </h4>
53 @li mddl_sas_cntlout.sas
54 @li mf_getuniquename.sas
55 @li mf_getvalue.sas
56 @li mf_islibds.sas
57 @li mf_nobs.sas
58 @li mp_abort.sas
59 @li mp_filtercheck.sas
60 @li mp_hashdataset.sas
61 @li mp_retainedkey.sas
62
63 <h4> Related Macros </h4>
64 @li mp_filtercheck.sas
65 @li mp_filtergenerate.sas
66 @li mp_filtervalidate.sas
67 @li mp_filterstore.test.sas
68
69 @version 9.2
70 @author [Allan Bowe](https://www.linkedin.com/in/allanbowe)
71
72**/
73
74%macro mp_filterstore(libds=,
75 queryds=work.filterquery,
76 filter_summary=PERM.FILTER_SUMMARY,
77 filter_detail=PERM.FILTER_DETAIL,
78 lock_table=PERM.LOCK_TABLE,
79 maxkeytable=PERM.MAXKEYTABLE,
80 outresult=work.result,
81 outquery=work.query,
82 mdebug=1
83);
84%put &sysmacroname entry vars:;
85%put _local_;
86
87%local ds0 ds1 ds2 ds3 ds4 filter_hash orig_libds;
88%let libds=%upcase(&libds);
89%let orig_libds=&libds;
90
91%mp_abort(iftrue= (&syscc ne 0)
92 ,mac=mp_filterstore
93 ,msg=%str(syscc=&syscc on macro entry)
94)
95%mp_abort(iftrue= (%mf_islibds(&filter_summary)=0)
96 ,mac=mp_filterstore
97 ,msg=%str(Invalid filter_summary value: &filter_summary)
98)
99%mp_abort(iftrue= (%mf_islibds(&filter_detail)=0)
100 ,mac=mp_filterstore
101 ,msg=%str(Invalid filter_detail value: &filter_detail)
102)
103%mp_abort(iftrue= (%mf_islibds(&lock_table)=0)
104 ,mac=mp_filterstore
105 ,msg=%str(Invalid lock_table value: &lock_table)
106)
107
108/**
109 * validate query
110 * use format catalog export, if a format
111 */
112%if "%substr(&libds,%length(&libds)-2,3)"="-FC" %then %do;
113 %let libds=%scan(&libds,1,-); /* chop off -FC extension */
114 %let ds0=%mf_getuniquename(prefix=fmtds_);
115 %let libds=&ds0;
116 /*
117 There is no need to export the entire format catalog here - the validations
118 are done against the data model, not the data values. So we can simply
119 hardcode the structure based on the cntlout dataset.
120 */
121 %mddl_sas_cntlout(libds=&ds0)
122
123%end;
124%mp_filtercheck(&queryds,targetds=&libds,abort=YES)
125
126/* hash the result */
127%let ds1=%mf_getuniquename(prefix=hashds);
128%mp_hashdataset(&queryds,outds=&ds1,salt=&orig_libds)
129%let filter_hash=%upcase(%mf_getvalue(&ds1,hashkey));
130%if &mdebug=1 %then %do;
131 data _null_;
132 putlog "filter_hash=&filter_hash";
133 set &ds1;
134 putlog (_all_)(=);
135 run;
136%end;
137
138/* check if data already exists for this hash */
139data &outresult;
140 set &filter_summary;
141 where filter_hash="&filter_hash";
142run;
143
144%mp_abort(iftrue= (&syscc ne 0)
145 ,mac=mp_filterstore
146 ,msg=%str(syscc=&syscc after hash check)
147)
148%mp_abort(iftrue= ("&filter_hash "=" ")
149 ,mac=mp_filterstore
150 ,msg=%str(problem with filter_hash generation)
151)
152
153%if %mf_nobs(&outresult)=0 %then %do;
154
155 /* first update summary table */
156 %let ds3=%mf_getuniquename(prefix=filtersum);
157 data work.&ds3;
158 if 0 then set &filter_summary;
159 filter_table="&orig_libds";
160 filter_hash="&filter_hash";
161 PROCESSED_DTTM=%sysfunc(datetime());
162 output;
163 stop;
164 run;
165
166 %mp_lockanytable(LOCK,
167 lib=%scan(&filter_summary,1,.)
168 ,ds=%scan(&filter_summary,2,.)
169 ,ref=MP_FILTERSTORE summary update - &filter_hash
170 ,ctl_ds=&lock_table
171 )
172
173 %let ds4=%mf_getuniquename(prefix=filtersumappend);
174 %mp_retainedkey(
175 base_lib=%scan(&filter_summary,1,.)
176 ,base_dsn=%scan(&filter_summary,2,.)
177 ,append_lib=work
178 ,append_dsn=&ds3
179 ,retained_key=filter_rk
180 ,business_key=filter_hash
181 ,maxkeytable=&maxkeytable
182 ,locktable=&lock_table
183 ,outds=work.&ds4
184 )
185 proc append base=&filter_summary data=&ds4;
186 run;
187
188 %mp_lockanytable(UNLOCK,
189 lib=%scan(&filter_summary,1,.)
190 ,ds=%scan(&filter_summary,2,.)
191 ,ref=MP_FILTERSTORE summary update - &filter_hash
192 ,ctl_ds=&lock_table
193 )
194
195 %if &syscc ne 0 %then %do;
196 data _null_;
197 set &ds4;
198 putlog (_all_)(=);
199 run;
200 %goto err;
201 %end;
202
203 data &outresult;
204 set &filter_summary;
205 where filter_hash="&filter_hash";
206 run;
207
208 /* Next, update detail table */
209 %let ds2=%mf_getuniquename(prefix=filterdetail);
210 data &ds2;
211 if 0 then set &filter_detail;
212 set &queryds;
213 format filter_hash $hex32. filter_line 8.;
214 filter_hash="&filter_hash";
215 filter_line=_n_;
216 PROCESSED_DTTM=%sysfunc(datetime());
217 run;
218 %mp_lockanytable(LOCK,
219 lib=%scan(&filter_detail,1,.)
220 ,ds=%scan(&filter_detail,2,.)
221 ,ref=MP_FILTERSTORE update - &filter_hash
222 ,ctl_ds=&lock_table
223 )
224 proc append base=&filter_detail data=&ds2;
225 run;
226
227 %mp_lockanytable(UNLOCK,
228 lib=%scan(&filter_detail,1,.)
229 ,ds=%scan(&filter_detail,2,.)
230 ,ref=MP_FILTERSTORE detail update &filter_hash
231 ,ctl_ds=&lock_table
232 )
233
234 %if &syscc ne 0 %then %do;
235 data _null_;
236 set &ds2;
237 putlog (_all_)(=);
238 run;
239 %goto err;
240 %end;
241
242%end;
243
244proc sort data=&filter_detail(where=(filter_hash="&filter_hash")) out=&outquery;
245 by filter_line;
246run;
247
248%err:
249%mp_abort(iftrue= (&syscc ne 0)
250 ,mac=mp_filterstore
251 ,msg=%str(syscc=&syscc on macro exit)
252)
253
254%mend mp_filterstore;