mpe_refreshcatalogs.sas
1 /**
2  @file mpe_refreshtables.sas
3  @brief Refreshes the data catalog
4  @details Assumes library is already assigned.
5  Usage:
6 
7  %mpe_refreshcatalogs(sashelp)
8 
9  <h4> SAS Macros </h4>
10  @li bitemporal_dataloader.sas
11 
12  @version 9.3
13  @author 4GL Apps Ltd
14 **/
15 
16 %macro mpe_refreshcatalogs(lib,cat=#all);
17 %let lib=%upcase(&lib);
18 %let cat=%upcase(&cat);
19 
20 %put running &sysmacroname &lib for &cat;
21 proc sql;
22 create table work.catdata as
23  select libname as libref,
24  memname,
25  objname,
26  objtype,
27  objdesc,
28  created,
29  modified,
30  alias,
31  level
32  from dictionary.catalogs
33  where upcase(libname)="&lib"
34 %if &cat ne #ALL %then %do;
35  and upcase(memname)="&cat"
36 %end;
37  ;
38 
39 %mp_abort(iftrue= (&syscc ne 0)
40  ,mac=&_program
41  ,msg=%str(syscc=&syscc afer &lib objects extraction)
42 )
43 
44 /* load mpe_datacatalog_CATS */
45 proc sql;
46 create table datacats as select distinct libref,memname from catdata;
47 
48 %bitemporal_dataloader(base_lib=&mpelib
49  ,base_dsn=mpe_datacatalog_CATS
50  ,append_dsn=datacats
51  ,PK=LIBREF MEMNAME
52  ,etlsource=&sysmacroname
53  ,loadtype=TXTEMPORAL
54  ,tech_from=TX_FROM
55  ,tech_to=TX_TO
56 %if &cat = #ALL %then %do;
57  ,close_vars=LIBREF
58 %end;
59  ,dclib=&mpelib
60 )
61 
62 /* load mpe_datacatalog_objsS */
63 proc sql;
64 create table dataobjs as
65  select distinct libref,
66  memname,
67  objname,
68  objtype,
69  objdesc,
70  alias
71  from catdata;
72 quit;
73 
74 %bitemporal_dataloader(base_lib=&mpelib
75  ,base_dsn=mpe_datacatalog_OBJS
76  ,append_dsn=dataobjs
77  ,PK=LIBREF MEMNAME OBJNAME OBJTYPE
78  ,etlsource=&sysmacroname
79  ,loadtype=TXTEMPORAL
80  ,tech_from=TX_FROM
81  ,tech_to=TX_TO
82 %if &cat = #ALL %then %do;
83  ,close_vars=LIBREF MEMNAME
84 %end;
85  ,dclib=&mpelib
86 )
87 
88 %put load mpe_datastatus_OBJS;
89 proc sql;
90 create table statusobjs as
91  select distinct libref,
92  memname,
93  objname,
94  objtype,
95  created,
96  modified,
97  level
98  from catdata;
99 
100 %bitemporal_dataloader(base_lib=&mpelib
101  ,base_dsn=mpe_datastatus_OBJS
102  ,append_dsn=statusobjs
103  ,PK=LIBREF MEMNAME OBJNAME OBJTYPE
104  ,etlsource=&sysmacroname
105  ,loadtype=TXTEMPORAL
106  ,tech_from=TX_FROM
107  ,tech_to=TX_TO
108 %if &cat = #ALL %then %do;
109  ,close_vars=LIBREF MEMNAME
110 %end;
111  ,dclib=&mpelib
112 )
113 
114 %put load mpe_datastatus_cats;
115 proc sql;
116 create table statuscats as
117  select libref,
118  memname,
119  count(*) as nobjs,
120  min(created) as created,
121  max(modified) as modified
122  from catdata
123  group by 1,2;
124 
125 %bitemporal_dataloader(base_lib=&mpelib
126  ,base_dsn=mpe_datastatus_cats
127  ,append_dsn=statuscats
128  ,PK=LIBREF MEMNAME
129  ,etlsource=&sysmacroname
130  ,loadtype=TXTEMPORAL
131  ,tech_from=TX_FROM
132  ,tech_to=TX_TO
133 %if &cat = #ALL %then %do;
134  ,close_vars=LIBREF
135 %end;
136  ,dclib=&mpelib
137 )
138 
139 
140 %mend mpe_refreshcatalogs;