mpe_refreshcatalogs.sas

/**
  @file mpe_refreshtables.sas
  @brief Refreshes the data catalog
  @details Assumes library is already assigned.
  Usage:
 
      %mpe_refreshcatalogs(sashelp)
 
  <h4> SAS Macros </h4>
  @li bitemporal_dataloader.sas
 
  @version 9.3
  @author 4GL Apps Ltd
**/
 
%macro mpe_refreshcatalogs(lib,cat=#all);
%let lib=%upcase(&lib);
%let cat=%upcase(&cat);
 
%put running &sysmacroname &lib for &cat;
proc sql;
create table work.catdata as
  select libname as libref,
    memname,
    objname,
    objtype,
    objdesc,
    created,
    modified,
    alias,
    level
  from dictionary.catalogs
  where upcase(libname)="&lib"
%if &cat ne #ALL %then %do;
    and upcase(memname)="&cat"
%end;
  ;
 
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=&_program
  ,msg=%str(syscc=&syscc afer &lib objects extraction)
)
 
/* load mpe_datacatalog_CATS */
proc sql;
create table datacats as select distinct libref,memname from catdata;
 
%bitemporal_dataloader(base_lib=&mpelib
  ,base_dsn=mpe_datacatalog_CATS
  ,append_dsn=datacats
  ,PK=LIBREF MEMNAME
  ,etlsource=&sysmacroname
  ,loadtype=TXTEMPORAL
  ,tech_from=TX_FROM
  ,tech_to=TX_TO
%if &cat = #ALL %then %do;
    ,close_vars=LIBREF
%end;
  ,dclib=&mpelib
)
 
/* load mpe_datacatalog_objsS */
proc sql;
create table dataobjs as
  select distinct libref,
    memname,
    objname,
    objtype,
    objdesc,
    alias
  from catdata;
quit;
 
%bitemporal_dataloader(base_lib=&mpelib
  ,base_dsn=mpe_datacatalog_OBJS
  ,append_dsn=dataobjs
  ,PK=LIBREF MEMNAME OBJNAME OBJTYPE
  ,etlsource=&sysmacroname
  ,loadtype=TXTEMPORAL
  ,tech_from=TX_FROM
  ,tech_to=TX_TO
%if &cat = #ALL %then %do;
    ,close_vars=LIBREF MEMNAME
%end;
  ,dclib=&mpelib
)
 
%put load mpe_datastatus_OBJS;
proc sql;
create table statusobjs as
  select distinct libref,
    memname,
    objname,
    objtype,
    created,
    modified,
    level
  from catdata;
 
%bitemporal_dataloader(base_lib=&mpelib
  ,base_dsn=mpe_datastatus_OBJS
  ,append_dsn=statusobjs
  ,PK=LIBREF MEMNAME OBJNAME OBJTYPE
  ,etlsource=&sysmacroname
  ,loadtype=TXTEMPORAL
  ,tech_from=TX_FROM
  ,tech_to=TX_TO
%if &cat = #ALL %then %do;
    ,close_vars=LIBREF MEMNAME
%end;
  ,dclib=&mpelib
)
 
%put load mpe_datastatus_cats;
proc sql;
create table statuscats as
  select libref,
    memname,
    count(*) as nobjs,
    min(created) as created,
    max(modified) as modified
  from catdata
  group by 1,2;
 
%bitemporal_dataloader(base_lib=&mpelib
  ,base_dsn=mpe_datastatus_cats
  ,append_dsn=statuscats
  ,PK=LIBREF MEMNAME
  ,etlsource=&sysmacroname
  ,loadtype=TXTEMPORAL
  ,tech_from=TX_FROM
  ,tech_to=TX_TO
%if &cat = #ALL %then %do;
    ,close_vars=LIBREF
%end;
  ,dclib=&mpelib
)
 
 
%mend mpe_refreshcatalogs;