/*-------------------------------------------------------------------------------------*\
| PROGRAM NAME: |
| scdm_data_qa_review-level2.sas |
| |
|---------------------------------------------------------------------------------------|
| PURPOSE: |
| The purpose of the program is to perform critical level 2 data quality checks |
| on all applicable tables. |
|---------------------------------------------------------------------------------------|
| PROGRAM INPUT: |
| see 00.0_scdm_data_qa_review_master_file.sas |
| |
| PROGRAM OUTPUT: |
| see Workplan PDF |
|---------------------------------------------------------------------------------------|
| CONTACT: |
| Sentinel Coordinating Center |
| info@sentinelsystem.org |
\*-------------------------------------------------------------------------------------*/
/*-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-;
* PLEASE DO NOT EDIT BELOW WITHOUT CONTACTING THE SENTINEL OPERATIONS CENTER ;
*-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-;
/**/
*------------------------------------------------------------------------------------- ;
* START Level 2 Macro ;
*------------------------------------------------------------------------------------- ;
%global dplocal_all ;
%let dplocal_all= ;
%macro agg_dplocal ;
%local t_module t_z ;
%if &SPAWN %then %do ;
%if %length(&module_list) > 0 %then %do;
%do t_z = 1 %to %sysfunc(countw(&module_list));
%let t_module = %scan(&module_list., &t_z.);
%if not %eval(%upcase(&t_module) in &module_nospawn) %then %do ;
%if %length(&dplocal_all) = 0 %then %let dplocal_all = "&dplocal.module_&t_module." ;
%else %let dplocal_all = &dplocal_all "&dplocal.module_&t_module." ;
%end ;
%end;
%end;
libname dplocal ("&dplocal" &dplocal_all) ;
/* create macro variables from spawned process in main program */
data _null_ ;
set dplocal.macvalue_: ;
by name offset ;
if first.name then call symputx(name,value,'G') ; %* ignore macvalue > 200 char long ;
;run;
proc datasets library=dplocal nolist nowarn;
delete macvalue_: ;
;quit;
/**/
%end ;
%else %do; /* if not spawned */
%let dplocal_all=&dplocal ;
%end ;
%put |&=dplocal_all| ;
%set_ds (libin=dplocal, dsin_prefix=all_l2_nobs_dup_, libout=dplocal, dsout=all_l2_nobs_dup);
%set_ds (libin=dplocal, dsin_prefix=all_l1_nobs_, libout=dplocal, dsout=all_l1_nobs);
%set_ds (libin=dplocal, dsin_prefix=all_l1_record_count_, libout=dplocal, dsout=all_l1_record_count);
%set_ds (libin=dplocal, dsin_prefix=all_l1_cont_, libout=dplocal, dsout=all_l1_cont);
%set_ds (libin=dplocal, dsin_prefix=all_l1_scdm_comp_, libout=dplocal, dsout=all_l1_scdm_comp);
%set_ds (libin=dplocal, dsin_prefix=all_l1_flags_, libout=dplocal, dsout=all_l1_flags);
%mend;
%agg_dplocal ;
%macro level2_abort;
/* Aggregate existing l1 individual cross-table variable datasets by unique values */
proc sql noprint;
select lowcase(memname), count(memname) into :mem separated by " ", :exist trimmed
from dictionary.members
where libname='DPLOCAL' and memtype='DATA' and substr(memname,1,1)="_"
;
quit;
/** Create Cross Table Datasets */
%put ====> Creating Cross Table Datasets;
data crosstable_index;
length location $ 16;
set infolder.lkp_all_l1(where=(lowcase(tabid) in (&sql_tabidlist) and crossvar='X'));
location = catx('.', "dplocal", cats("_", catx('_', varid, tabid)));
keep tabid variable varid varlength ValidValueType location;
run;
data _null_;
set crosstable_index;
call execute('%nrstr(%create_xtable_length_ds(tabid_val=' || tabid || ',var_val='
|| variable || ',indsn=' || location || '))');
run;
/** Create Cross Table Match Datasets */
proc sort data=crosstable_index;
by variable;
run;
proc transpose data=crosstable_index out=crosstable_index_1(drop=_NAME_) prefix=loc;
by variable;
var location;
run;
proc transpose data=crosstable_index out=crosstable_index_2(drop=_NAME_) prefix=tab_;
by variable;
var tabid;
run;
data crosstable_index_3;
length location $ 255 tabid $ 84;
merge crosstable_index_1 crosstable_index_2;
by variable;
location=catx(' ', of loc:);
tabid=strip(catx(',', of tab_:));
keep variable location tabid;
call execute('%nrstr(%create_xtable_match_ds(var_val=' || variable
|| ', tab_val = %str(' || tabid || '), indsn = ' || location || '))');
if &do_partitions and lowcase(variable) not in (&sql_varname_exc) then do ;
call execute('%nrstr(%ck_xvar_uniq(var_val=' || variable || '))');
end;
run;
/** Clean-up */
%set_ds (libin=dplocal, dsin_prefix=tmp_length_, libout=dplocal, dsout=all_l2_crosstab_length_value);
proc datasets lib=work memtype=data nolist nodetails nowarn;
delete tmp_:;
delete crosstable_:
quit;
proc datasets lib=dplocal memtype=data nolist nodetails nowarn;
delete tmp:;
delete _:;
quit;
%put ***************** CREATE CROSSTABLE DATASETS COMPLETE *********************;
/*-----------------------------------------------------------------------------------*/
/* 1.1 - Create temporary lookup table */
/*-----------------------------------------------------------------------------------*/
data temp_l2_flags;
length table1 table2 $3;
set infolder.lkp_all_flags(where=(level='2' and substr(lowcase(tableid),1,3) in (&sql_tabidlist)
and substr(lowcase(tableid),5,3) in (' ' ,&sql_tabidlist)
));
table1=substr(tableid,1,3);
table2=substr(tableid,5,3);
if abortYN='Y' and ^index(tableid,'-') then output temp_l2_flags;
if abortYN='Y' and index(tableid,'-') then output temp_l2_flags;
if checkid in('209', '236', '237') then output temp_l2_flags;
if abortyn='N' and index(tableid,'-') then output temp_l2_flags;
run;
proc sort data = temp_l2_flags nodupkey out=unique_l2_checkids(keep=checkid);
by checkid;
run;
data _null_;
set unique_l2_checkids;
call execute(cats('%nrstr(%flag_',checkid,';)'));
run;
data dplocal._l2_flags_temp;
set flag_l2:;
where count > 0;
run;
proc datasets lib=work memtype=data kill nolist nowarn nodetails;
quit;
/* get count of abortYN=Y flags captured in dplocal.l2_flags_temp ds */
proc sql noprint;
select count(*) into :abort_qa
from dplocal._l2_flags_temp (where=(lowcase(abortYN)='y'))
;
quit;
/* End macro at current loop when first AbortYN = Y occurs and output
finalized L2 flags for DP review */
%if &abort_qa. ne 0 %then %do;
%set_ds (libin=dplocal, dsin_prefix=_l2_flags_, libout=dplocal, dsout=all_l2_flags);
%let end_qa=1;
%let l=999;
data _null_;
putlog 70*'!';
putlog 'ERR'"OR: The &module. module detected fatal L2 data flags";
putlog " that require the QA package to abort";
putlog 70*'!';
run;
proc datasets lib=dplocal nolist nowarn nodetails;
delete tmp_: l2_nodup_:;
quit;
%end; /*end condition: abort_qa ne 0 */
%else %do; /* otherwise save l2 warn flags for later use */
data dplocal.l2_flags_temp;
set dplocal.l2_flags_temp
dplocal._l2_flags_temp;
run;
/* cleanup temporary ds */
proc datasets lib=dplocal nolist;
delete _l2_flags_temp;
quit;
%end;
/* Clean up datasets from DPLOCAL before continuing */
proc datasets lib=dplocal memtype=data nowarn nolist nodetails;
delete tmp_: ;
quit;
%mend;
%level2_abort;
*-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-;
* END scdm-data-qa-review-level2.sas ;
*-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-;