mpe_getversions.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Get previous versions of a table
4  @details Used to fetch version data for a particular table
5  Delivered as part of this issue: https://git.datacontroller.io/dc/dc/issues/84
6 
7  @param [in] dclib The DC libref
8  @param [in] lib The library of the dataset for which to fetch versions
9  @param [in] ds The dataset to fetch versions for
10  @param [out] outds= (work.mpe_getversions) the DS to create
11 
12 **/
13 
14 
15 %macro mpe_getversions(dclib,libref,ds,outds=work.mpe_getversions);
16 
17  proc sql;
18  create table &outds as
19  select a.table_id as LOAD_REF
20  ,a.reviewed_by_nm as user_nm
21  ,a.reviewed_on_dttm as version_dttm_num
22  ,put(a.reviewed_on_dttm,datetime19.) as VERSION_DTTM
23  ,a.submitted_reason_txt as VERSION_DESC
24  ,b.changed_records
25  ,b.new_records
26  ,b.deleted_records
27  from &dclib..mpe_submit a
28  left join &dclib..MPE_DATALOADS(where=(libref="&libref" & dsn="&ds")) b
29  on a.table_id=b.etlsource
30  where a.base_lib="&libref" and a.base_ds="&ds"
31  and a.submit_status_cd='APPROVED' and a.num_of_approvals_remaining=0
32  order by a.reviewed_on_dttm desc;
33 
34 %mend mpe_getversions;