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 
29 data &outlib..class(index=(name /unique));
30  set sashelp.class;
31 run;
32 
33 data &outlib..cars(index=(carspk=(make model drivetrain) /unique));
34  set sashelp.cars;
35 run;
36 
37 data &outlib..springs(index=(springspk=(name area latitude) /unique));
38  set sashelp.springs;
39 run;
40 
41 data &outlib..fmt_checks;;
42  pk=1; E8601DA=date();
43  format E8601DA E8601DA10.;
44 run;
45 
46 data 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;
57 run;
58 proc append base=&dc_libref..MPE_TABLES data=&syslast;
59 run;
60 
61 /**
62  * DC data extra
63  */
64 %local lib;
65 %let lib=&dc_libref;
66 proc sql;
67 insert 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;
82 insert 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;
97 insert 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;
112 insert 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;
127 insert 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;
142 insert 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;
157 insert 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;
172 insert 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;
187 insert 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;
202 insert 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;
217 insert 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;
232 insert 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;
247 insert 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;
262 insert 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;
277 insert 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;
292 insert 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;
307 insert 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 */
325 insert 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 */
335 insert 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;
342 insert 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 
350 data 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;
358 run;
359 proc append base=&dc_libref..MPE_TABLES data=&syslast;
360 run;
361 
362 %mend mpe_makesampledata;