mpe_getversions.sas

Go to the documentation of this file.

/**
  @file
  @brief Get previous versions of a table
  @details  Used to fetch version data for a particular table
  Delivered as part of this issue: https://git.datacontroller.io/dc/dc/issues/84
 
  @param [in] dclib The DC libref
  @param [in] lib The library of the dataset for which to fetch versions
  @param [in] ds The dataset to fetch versions for
  @param [out] outds= (work.mpe_getversions) the DS to create
 
**/
 
 
%macro mpe_getversions(dclib,libref,ds,outds=work.mpe_getversions);
 
  proc sql;
  create table &outds as
    select a.table_id as LOAD_REF
      ,a.reviewed_by_nm as user_nm
      ,a.reviewed_on_dttm as version_dttm_num
      ,put(a.reviewed_on_dttm,datetime19.) as VERSION_DTTM
      ,a.submitted_reason_txt as VERSION_DESC
      ,b.changed_records
      ,b.new_records
      ,b.deleted_records
    from &dclib..mpe_submit a
    left join &dclib..MPE_DATALOADS(where=(libref="&libref" & dsn="&ds")) b
    on a.table_id=b.etlsource
    where a.base_lib="&libref" and a.base_ds="&ds"
      and a.submit_status_cd='APPROVED' and a.num_of_approvals_remaining=0
    order by a.reviewed_on_dttm desc;
 
%mend mpe_getversions;