Loading...
Searching...
No Matches
mpe_filtermaster.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief dynamic where clause creation
4 @details Generates a where clause based on the following inputs:
5
6 @li The filter_RK (if provided)
7 @li The mode (if EDIT then filter for current records)
8 @li The user permissions (Row Level Security)
9
10 This macro should be used whenever surfacing data to the user. Note that
11 it is not possible to %include filerefs directly in a proc sql where clause.
12 The workaround is to use a data step view.
13
14 Note - DCLIB should be assigned.
15
16 @param [in] mode The mode. If EDIT, then current rows are returned. Valid
17 Values:
18 @li EDIT
19 @li VIEW
20 @li DLOAD - used in getrawdata
21 @li ULOAD - used by stagedata.sas to prevent restricted rows being submitted
22
23 @param [in] libds The target libref.dataset to which the filter will apply.
24 @param [in] filter_rk= (-1) The filter_rk, if available
25 @param [in] dclib= The libref of the DC control tables
26 @param [out] outref= The output fileref to create (containing the filter)
27 @param [out] outds= (work.query) The query dataset (if filter_rk supplied)
28
29 <h4> SAS Macros </h4>
30 @li mf_fmtdttm.sas
31 @li mf_getuser.sas
32 @li mf_getuniquefileref.sas
33 @li mf_getuniquename.sas
34 @li mf_nobs.sas
35 @li mp_abort.sas
36 @li mp_filtergenerate.sas
37 @li mpe_getgroups.sas
38
39 @version 9.3
40 @author 4GL Apps Ltd
41 @copyright 4GL Apps Ltd. This code may only be used within Data Controller
42 and may not be re-distributed or re-sold without the express permission of
43 4GL Apps Ltd.
44**/
45
46%macro mpe_filtermaster(mode,libds,
47 dclib=,
48 filter_rk=-1,
49 outref=0,
50 outds=work.query
51);
52
53%put &sysmacroname entry vars:;
54%put _local_;
55
56%let mode=%upcase(&mode);
57%let libds=%upcase(&libds);
58
59
60%mp_abort(iftrue= (
61 &mode ne EDIT and &mode ne VIEW and &mode ne DLOAD and &mode ne ULOAD
62 )
63 ,mac=&sysmacroname
64 ,msg=%str(Invalid MODE: &mode)
65)
66%mp_abort(iftrue= (&outref = 0)
67 ,mac=&sysmacroname
68 ,msg=%str(Please provide a fileref!)
69)
70%mp_abort(iftrue= (&syscc ne 0)
71 ,mac=&sysmacroname
72 ,msg=%str(syscc=&syscc)
73)
74
75filename &outref temp;
76
77/* ensure outputs exist */
78data _null_;
79 file &outref;
80 put ' ';
81run;
82data &outds;
83 set &dclib..mpe_filtersource;
84 stop;
85run;
86
87/**
88 * Deal with FILTER_RK first
89 */
90%if &filter_rk gt 0 %then %do;
91
92 data _null_;
93 file &outref;
94 put '( '@@;
95 set &dclib..mpe_filteranytable(where=(filter_rk=&filter_rk));
96 call symputx('filter_hash',filter_hash,'l');
97 run;
98
99 proc sort data=&dclib..mpe_filtersource(where=(filter_hash="&filter_hash"))
100 out=&outds(drop=filter_hash filter_line processed_dttm);
101 by filter_line;
102 run;
103 %mp_filtergenerate(&outds,outref=&outref)
104
105%end;
106
107/* Now filter for current records if the MODE is EDIT or DLOAD */
108%local varfrom varto;
109%let varfrom=0;
110
111proc sql;
112select coalescec(var_txfrom,'0'), var_txto into: varfrom,:varto
113 from &dclib..MPE_TABLES
114 where &dc_dttmtfmt. lt tx_to
115 and libref="%scan(&libds,1,.)" and dsn="%scan(&libds,2,.)";
116
117%put &=varfrom;
118%put &=varto;
119
120/**
121 * Check if the date variables were mentioned in the query
122 * This is a trigger for serving a historical view instead of current
123 * we skip this part when checking an ULOAD as there are no date vars
124 */
125%if &varfrom ne 0 and (&mode=EDIT or &mode=DLOAD) %then %do;
126 %local validityvars;
127 proc sql;
128 select count(*) into: validityvars
129 from &outds
130 where variable_nm in ("&varfrom","&varto");
131 %if &validityvars=0 %then %do;
132 data _null_;
133 file &outref mod;
134 length filter_text $32767;
135 varfrom=symget('varfrom');
136 varto=symget('varto');
137 filter_text=catx(' ',
138 '("%sysfunc(datetime(),',"%mf_fmtdttm()",')"dt <',varto,')'
139 );
140 if &filter_rk > 0 then put 'AND ' filter_text;
141 else put filter_text;
142 run;
143 %end;
144%end;
145
146/**
147 * Now do Row Level Security based on the MPE_ROW_LEVEL_SECURITY table
148 */
149
150/* first determine users group membership */
151%mpe_getgroups(user=%mf_getuser(),outds=work.groups)
152%local admin_check;
153proc sql;
154select count(*) into: admin_check
155 from work.groups
156 where groupname="&mpeadmins";
157
158%put &sysmacroname: &=admin_check &=mpeadmins;
159%if &admin_check=0 %then %do;
160 %local scopeval;
161 %if &mode=DLOAD %then %let scopeval=VIEW;
162 %if &mode=ULOAD %then %let scopeval=EDIT;
163 %else %let scopeval=&mode;
164 /* extract relevant rows */
165 %local rlsds;
166 %let rlsds=%mf_getuniquename();
167 proc sql;
168 create table work.&rlsds as
169 select rls_group,
170 rls_group_logic as group_logic,
171 rls_subgroup_logic as subgroup_logic,
172 rls_subgroup_id as subgroup_id,
173 rls_variable_nm as variable_nm,
174 rls_operator_nm as operator_nm,
175 rls_raw_value as raw_value
176 from &mpelib..mpe_row_level_security
177 where &dc_dttmtfmt. lt tx_to
178 and rls_scope in ("&scopeval",'ALL')
179 and upcase(rls_group) in (select upcase(groupname) from work.groups)
180 and rls_libref="%scan(&libds,1,.)"
181 and rls_table="%scan(&libds,2,.)"
182 and rls_active=1
183 order by rls_group,rls_subgroup_id;
184 %if &sqlobs>0 %then %do;
185 /* check if we currently have filter or not */
186 data ;
187 infile &outref end=eof;
188 input;
189 if _n_=1 and eof and cats(_infile_)='' then newfilter=1;
190 output;
191 stop;
192 run;
193 data _null_;
194 set &syslast;
195 file &outref mod;
196 if newfilter=1 then put '(';
197 else put 'AND (';
198 run;
199
200 /* loop through and apply filters for each group membership */
201 %local fref ds;
202 %let fref=%mf_getuniquefileref();
203 %let ds=%mf_getuniquename();
204
205 proc sql noprint;
206 select distinct rls_group into : group1 -
207 from work.&rlsds;
208
209 %do i=1 %to &sqlobs;
210 data work.&ds;
211 set work.&rlsds;
212 where rls_group="&&group&i";
213 drop rls_group;
214 run;
215 %mp_filtergenerate(&ds,outref=&fref)
216 data _null_;
217 infile &fref;
218 file &outref mod;
219 input;
220 if &i>1 and _n_=1 then put ' OR ';
221 put _infile_;
222 run;
223 %end;
224 data _null_;
225 file &outref mod;
226 put ')';
227 run;
228 %end; /* &sqlobs>0 */
229 %else %do;
230 %put &sysmacroname: no matching groups;
231 data _null_;
232 set work.groups;
233 putlog (_all_)(=);
234 run;
235 %end;
236 %mp_abort(iftrue= (&syscc>0)
237 ,mac=&sysmacroname
238 ,msg=%str(Row Level Security Generation Error)
239 )
240%end; /* &admin_check=0 */
241
242%put leaving &sysmacroname with the following query:;
243
244%local empty;
245%let empty=0;
246data _null_;
247 infile &outref end=eof;
248 input;
249 putlog _infile_;
250 if _n_=1 and eof and cats(_infile_)='' then do;
251 put '1=1';
252 call symputx('empty',1,'l');
253 end;
254run;
255%if &empty=1 %then %do;
256 data _null_;
257 file &outref;
258 put '1=1';
259 run;
260%end;
261
262%mend mpe_filtermaster;