Loading...
Searching...
No Matches
mpe_makesampledata.sas
Go to the documentation of this file.
1/**
2 @file mpe_makesampledata.sas
3 @brief Creates sample data for DC and updates MPE_TABLES
4 @details Creates sample data for DC.
5
6 Usage:
7
8 %mpe_makesampledata(outlib=DCxxxx)
9
10 <h4> SAS Macros </h4>
11
12 @version 9.2
13 @author 4GL Apps Ltd
14 @copyright 4GL Apps Ltd. This code may only be used within Data Controller
15 and may not be re-distributed or re-sold without the express permission of
16 4GL Apps Ltd.
17**/
18
19%macro mpe_makesampledata(outlib=);
20%if &syscc ne 0 %then %do;
21 %put syscc=&syscc exiting &sysmacroname;
22 %return;
23%end;
24%if &syssite ne 70221618 and &syssite ne 70253615 %then %do;
25 %put syssite=&syssite, exiting &sysmacroname;
26 %return;
27%end;
28
29data &outlib..class(index=(name /unique));
30 set sashelp.class;
31run;
32
33data &outlib..cars(index=(carspk=(make model drivetrain) /unique));
34 set sashelp.cars;
35run;
36
37data &outlib..springs(index=(springspk=(name area latitude) /unique));
38 set sashelp.springs;
39run;
40
41data &outlib..fmt_checks;;
42 pk=1; E8601DA=date();
43 format E8601DA E8601DA10.;
44run;
45
46data append;
47 if 0 then set &dc_libref..mpe_tables;
48 TX_FROM=0;
49 TX_TO='31DEC9999:23:59:59'dt;
50 LIBREF=%upcase("&outlib");
51 LOADTYPE='UPDATE';
52 NUM_OF_APPROVALS_REQUIRED=1;
53 DSN='SPRINGS'; BUSKEY='NAME AREA LATITUDE'; output;
54 DSN='CARS'; BUSKEY='MAKE MODEL DRIVETRAIN'; output;
55 DSN='CLASS'; BUSKEY='NAME'; output;
56 DSN='FMT_CHECKS'; BUSKEY='PK'; output;
57run;
58proc append base=&dc_libref..MPE_TABLES data=&syslast;
59run;
60
61/**
62 * DC data extra
63 */
64%local lib;
65%let lib=&dc_libref;
66proc sql;
67insert into &lib..mpe_row_level_security set
68 tx_from=0
69 ,tx_to='31DEC5999:23:59:59'dt
70 ,RLS_RK=4
71 ,RLS_SCOPE='ALL'
72 ,RLS_GROUP='sec-sas9-prd-int-sasplatform-300114sasjs'
73 ,RLS_LIBREF="&lib."
74 ,RLS_TABLE="MPE_TABLES"
75 ,RLS_GROUP_LOGIC='AND'
76 ,RLS_SUBGROUP_LOGIC='OR'
77 ,RLS_SUBGROUP_ID=0
78 ,RLS_VARIABLE_NM='NUM_OF_APPROVALS_REQUIRED'
79 ,RLS_OPERATOR_NM='>'
80 ,RLS_RAW_VALUE='0'
81 ,RLS_ACTIVE=1;
82insert into &lib..mpe_row_level_security set
83 tx_from=0
84 ,tx_to='31DEC5999:23:59:59'dt
85 ,RLS_RK=5
86 ,RLS_SCOPE='ALL'
87 ,RLS_GROUP='sec-sas9-prd-int-sasplatform-300114sasjs'
88 ,RLS_LIBREF="&lib."
89 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
90 ,RLS_GROUP_LOGIC='AND'
91 ,RLS_SUBGROUP_LOGIC='OR'
92 ,RLS_SUBGROUP_ID=1
93 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
94 ,RLS_OPERATOR_NM='NOT IN'
95 ,RLS_RAW_VALUE="('N/A1','N/A2')"
96 ,RLS_ACTIVE=1;
97insert into &lib..mpe_row_level_security set
98 tx_from=0
99 ,tx_to='31DEC5999:23:59:59'dt
100 ,RLS_RK=6
101 ,RLS_SCOPE='ALL'
102 ,RLS_GROUP='sec-sas9-prd-int-sasplatform-300114sasjs'
103 ,RLS_LIBREF="&lib."
104 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
105 ,RLS_GROUP_LOGIC='AND'
106 ,RLS_SUBGROUP_LOGIC='OR'
107 ,RLS_SUBGROUP_ID=1
108 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
109 ,RLS_OPERATOR_NM='NOT IN'
110 ,RLS_RAW_VALUE="('N/A1','N/A2','N/A3')"
111 ,RLS_ACTIVE=1;
112insert into &lib..mpe_row_level_security set
113 tx_from=0
114 ,tx_to='31DEC5999:23:59:59'dt
115 ,RLS_RK=7
116 ,RLS_SCOPE='ALL'
117 ,RLS_GROUP='sec-sas9-prd-int-sasplatform-300114sasjs'
118 ,RLS_LIBREF="&lib."
119 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
120 ,RLS_GROUP_LOGIC='AND'
121 ,RLS_SUBGROUP_LOGIC='OR'
122 ,RLS_SUBGROUP_ID=2
123 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
124 ,RLS_OPERATOR_NM='NOT IN'
125 ,RLS_RAW_VALUE="('N/A1','N/A2')"
126 ,RLS_ACTIVE=1;
127insert into &lib..mpe_row_level_security set
128 tx_from=0
129 ,tx_to='31DEC5999:23:59:59'dt
130 ,RLS_RK=8
131 ,RLS_SCOPE='ALL'
132 ,RLS_GROUP='sec-sas9-prd-int-sasplatform-300114sasjs'
133 ,RLS_LIBREF="&lib."
134 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
135 ,RLS_GROUP_LOGIC='AND'
136 ,RLS_SUBGROUP_LOGIC='OR'
137 ,RLS_SUBGROUP_ID=3
138 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
139 ,RLS_OPERATOR_NM='NOT IN'
140 ,RLS_RAW_VALUE="('N/A1','N/A2')"
141 ,RLS_ACTIVE=1;
142insert into &lib..mpe_row_level_security set
143 tx_from=0
144 ,tx_to='31DEC5999:23:59:59'dt
145 ,RLS_RK=9
146 ,RLS_SCOPE='ALL'
147 ,RLS_GROUP='sec-sas9-prd-int-sasplatform-300114sasjs'
148 ,RLS_LIBREF="&lib."
149 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
150 ,RLS_GROUP_LOGIC='AND'
151 ,RLS_SUBGROUP_LOGIC='OR'
152 ,RLS_SUBGROUP_ID=4
153 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
154 ,RLS_OPERATOR_NM='NOT IN'
155 ,RLS_RAW_VALUE="('N/A1','N/A2')"
156 ,RLS_ACTIVE=1;
157insert into &lib..mpe_row_level_security set
158 tx_from=0
159 ,tx_to='31DEC5999:23:59:59'dt
160 ,RLS_RK=10
161 ,RLS_SCOPE='ALL'
162 ,RLS_GROUP='sec-sas9-prd-int-sasplatform-300114sasjs'
163 ,RLS_LIBREF="&lib."
164 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
165 ,RLS_GROUP_LOGIC='AND'
166 ,RLS_SUBGROUP_LOGIC='OR'
167 ,RLS_SUBGROUP_ID=5
168 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
169 ,RLS_OPERATOR_NM='NOT IN'
170 ,RLS_RAW_VALUE="('N/A1','N/A2','N/A3','N/A4','N/A5','N/A6','N/A7')"
171 ,RLS_ACTIVE=1;
172insert into &lib..mpe_row_level_security set
173 tx_from=0
174 ,tx_to='31DEC5999:23:59:59'dt
175 ,RLS_RK=11
176 ,RLS_SCOPE='ALL'
177 ,RLS_GROUP='sec-sas9-prd-int-sasplatform-300114sasjs'
178 ,RLS_LIBREF="&lib."
179 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
180 ,RLS_GROUP_LOGIC='AND'
181 ,RLS_SUBGROUP_LOGIC='OR'
182 ,RLS_SUBGROUP_ID=6
183 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
184 ,RLS_OPERATOR_NM='NOT IN'
185 ,RLS_RAW_VALUE="('N/A1','N/A2','N/A3','N/A4','N/A5','N/A6','N/A7')"
186 ,RLS_ACTIVE=1;
187insert into &lib..mpe_row_level_security set
188 tx_from=0
189 ,tx_to='31DEC5999:23:59:59'dt
190 ,RLS_RK=12
191 ,RLS_SCOPE='ALL'
192 ,RLS_GROUP='sec-sas9-prd-int-sasplatform-300114sasjs'
193 ,RLS_LIBREF="&lib."
194 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
195 ,RLS_GROUP_LOGIC='AND'
196 ,RLS_SUBGROUP_LOGIC='OR'
197 ,RLS_SUBGROUP_ID=7
198 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
199 ,RLS_OPERATOR_NM='NOT IN'
200 ,RLS_RAW_VALUE="('N/A1','N/A2','N/A3','N/A4','N/A5','N/A6','N/A7')"
201 ,RLS_ACTIVE=1;
202insert into &lib..mpe_row_level_security set
203 tx_from=13
204 ,tx_to='31DEC5999:23:59:59'dt
205 ,RLS_RK=5
206 ,RLS_SCOPE='ALL'
207 ,RLS_GROUP='sec-sas9-prd-ext-sasplatform-300114sasjs'
208 ,RLS_LIBREF="&lib."
209 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
210 ,RLS_GROUP_LOGIC='AND'
211 ,RLS_SUBGROUP_LOGIC='OR'
212 ,RLS_SUBGROUP_ID=1
213 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
214 ,RLS_OPERATOR_NM='NOT IN'
215 ,RLS_RAW_VALUE="('N/A1','N/A2')"
216 ,RLS_ACTIVE=1;
217insert into &lib..mpe_row_level_security set
218 tx_from=14
219 ,tx_to='31DEC5999:23:59:59'dt
220 ,RLS_RK=6
221 ,RLS_SCOPE='ALL'
222 ,RLS_GROUP='sec-sas9-prd-ext-sasplatform-300114sasjs'
223 ,RLS_LIBREF="&lib."
224 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
225 ,RLS_GROUP_LOGIC='AND'
226 ,RLS_SUBGROUP_LOGIC='OR'
227 ,RLS_SUBGROUP_ID=1
228 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
229 ,RLS_OPERATOR_NM='NOT IN'
230 ,RLS_RAW_VALUE="('N/A1','N/A2','N/A3')"
231 ,RLS_ACTIVE=1;
232insert into &lib..mpe_row_level_security set
233 tx_from=15
234 ,tx_to='31DEC5999:23:59:59'dt
235 ,RLS_RK=7
236 ,RLS_SCOPE='ALL'
237 ,RLS_GROUP='sec-sas9-prd-ext-sasplatform-300114sasjs'
238 ,RLS_LIBREF="&lib."
239 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
240 ,RLS_GROUP_LOGIC='AND'
241 ,RLS_SUBGROUP_LOGIC='OR'
242 ,RLS_SUBGROUP_ID=2
243 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
244 ,RLS_OPERATOR_NM='NOT IN'
245 ,RLS_RAW_VALUE="('N/A1','N/A2')"
246 ,RLS_ACTIVE=1;
247insert into &lib..mpe_row_level_security set
248 tx_from=16
249 ,tx_to='31DEC5999:23:59:59'dt
250 ,RLS_RK=8
251 ,RLS_SCOPE='ALL'
252 ,RLS_GROUP='sec-sas9-prd-ext-sasplatform-300114sasjs'
253 ,RLS_LIBREF="&lib."
254 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
255 ,RLS_GROUP_LOGIC='AND'
256 ,RLS_SUBGROUP_LOGIC='OR'
257 ,RLS_SUBGROUP_ID=3
258 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
259 ,RLS_OPERATOR_NM='NOT IN'
260 ,RLS_RAW_VALUE="('N/A1','N/A2')"
261 ,RLS_ACTIVE=1;
262insert into &lib..mpe_row_level_security set
263 tx_from=17
264 ,tx_to='31DEC5999:23:59:59'dt
265 ,RLS_RK=9
266 ,RLS_SCOPE='ALL'
267 ,RLS_GROUP='sec-sas9-prd-ext-sasplatform-300114sasjs'
268 ,RLS_LIBREF="&lib."
269 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
270 ,RLS_GROUP_LOGIC='AND'
271 ,RLS_SUBGROUP_LOGIC='OR'
272 ,RLS_SUBGROUP_ID=4
273 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
274 ,RLS_OPERATOR_NM='NOT IN'
275 ,RLS_RAW_VALUE="('N/A1','N/A2')"
276 ,RLS_ACTIVE=1;
277insert into &lib..mpe_row_level_security set
278 tx_from=18
279 ,tx_to='31DEC5999:23:59:59'dt
280 ,RLS_RK=10
281 ,RLS_SCOPE='ALL'
282 ,RLS_GROUP='sec-sas9-prd-ext-sasplatform-300114sasjs'
283 ,RLS_LIBREF="&lib."
284 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
285 ,RLS_GROUP_LOGIC='AND'
286 ,RLS_SUBGROUP_LOGIC='OR'
287 ,RLS_SUBGROUP_ID=5
288 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
289 ,RLS_OPERATOR_NM='NOT IN'
290 ,RLS_RAW_VALUE="('N/A1','N/A2','N/A3','N/A4','N/A5','N/A6','N/A7')"
291 ,RLS_ACTIVE=1;
292insert into &lib..mpe_row_level_security set
293 tx_from=0
294 ,tx_to='31DEC5999:23:59:59'dt
295 ,RLS_RK=19
296 ,RLS_SCOPE='ALL'
297 ,RLS_GROUP='sec-sas9-prd-ext-sasplatform-300114sasjs'
298 ,RLS_LIBREF="&lib."
299 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
300 ,RLS_GROUP_LOGIC='AND'
301 ,RLS_SUBGROUP_LOGIC='OR'
302 ,RLS_SUBGROUP_ID=6
303 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
304 ,RLS_OPERATOR_NM='NOT IN'
305 ,RLS_RAW_VALUE="('N/A1','N/A2','N/A3','N/A4','N/A5','N/A6','N/A7')"
306 ,RLS_ACTIVE=1;
307insert into &lib..mpe_row_level_security set
308 tx_from=0
309 ,tx_to='31DEC5999:23:59:59'dt
310 ,RLS_RK=20
311 ,RLS_SCOPE='ALL'
312 ,RLS_GROUP='sec-sas9-prd-ext-sasplatform-300114sasjs'
313 ,RLS_LIBREF="&lib."
314 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
315 ,RLS_GROUP_LOGIC='AND'
316 ,RLS_SUBGROUP_LOGIC='OR'
317 ,RLS_SUBGROUP_ID=7
318 ,RLS_VARIABLE_NM='RLS_GROUP_LOGIC'
319 ,RLS_OPERATOR_NM='NOT IN'
320 ,RLS_RAW_VALUE="('N/A1','N/A2','N/A3','N/A4','N/A5','N/A6','N/A7')"
321 ,RLS_ACTIVE=1;
322
323
324/** create excel config */
325insert into &lib..MPE_EXCEL_CONFIG set
326 tx_from=0
327 ,xl_libref="&lib"
328 ,xl_table="MPE_DATADICTIONARY"
329 ,xl_column="DD_LONGDESC"
330 ,xl_rule="FORMULA"
331 ,xl_active=1
332 ,tx_to='31DEC5999:23:59:59'dt;
333
334/** mpe_security table */
335insert into &lib..mpe_security set
336 tx_from=0
337 ,libref="*ALL*"
338 ,dsn="*ALL*"
339 ,access_level="APPROVE"
340 ,sas_group="303001.DataController"
341 ,tx_to='31DEC5999:23:59:59'dt;
342insert into &lib..mpe_security set
343 tx_from=0
344 ,libref="*ALL*"
345 ,dsn="*ALL*"
346 ,access_level="EDIT"
347 ,sas_group="303001.DataController"
348 ,tx_to='31DEC5999:23:59:59'dt;
349
350data append;
351 if 0 then set &dc_libref..mpe_tables;
352 TX_FROM=0;
353 TX_TO='31DEC9999:23:59:59'dt;
354 LIBREF=%upcase("&dc_libref");
355 LOADTYPE='UPDATE';
356 NUM_OF_APPROVALS_REQUIRED=1;
357 DSN='MPE_USERS'; BUSKEY='USER_ID'; output;
358run;
359proc append base=&dc_libref..MPE_TABLES data=&syslast;
360run;
361
362%mend mpe_makesampledata;