/*____________________________________________________________________ ¦ MACRO: LINK ¦ ¦ JOB: Data Quality ¦ ¦ PROGRAMMER: Mahmoud Azimaee ¦ ¦ DATE: April 2011 ¦ ¦ DESCRIPTION: For a series of tables (or members of a cluster) ¦ ¦ this Macro creates a table showing linkability of ¦ ¦ datasets as indicated in the DQ Report Template. ¦ ¦ The table will be shown on screen and also will be ¦ ¦ saved in Excel format in /dq/saswork/[USERNAME]/ ¦ ¦ under name of [DOMAIN]_[DB]_LINKABILITY.xls ¦ ¦ This macro also generates a frequency table for PHIN¦ ¦ Type (SCRPHINTYPE) and which will be saved in Excel ¦ ¦ format under name of [DOMAIN]_[DB]_PHINTYPE.xls ¦ ¦ PARAMETERS: DOMAIN= Database domain on SPDS ¦ ¦ DB= Database prefix (or full name of cluster) ¦ ¦ PHIN= Name of PHIN variable(Default=SCRPHIN) ¦ ¦ TYPE= Name of PHINTYPE variable(Default=SCRPHINTYPE)¦ ¦ CLUSTER= If you want the LINKAGE Table by cluster ¦ ¦ members then assign YES (Default=NO) ¦ ¦ EXAMPLE: %LINK (health,MHCPL); ¦ ¦ %LINK (health,MHCPL, PHIN=filephin); ¦ ¦ %LINK (DOMAIN=social,DB=hcm_edi_2006jan, ¦ ¦ PHIN=FILEPHIN, TYPE=FILEPHINTYPE); ¦ ¦ %LINK (DOMAIN=social,DB=hcm_edi_2006jan, ¦ ¦ PHIN=FILEPHIN, TYPE=FILEPHINTYPE, ¦ ¦ CLUSTER=YES); ¦ ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯*/ **********************************************************************; %MACRO LINK (DOMAIN,DB, PHIN=SCRPHIN, TYPE=SCRPHINTYPE, CLUSTER=NO); %PUT ___________________________________________________________________; %PUT ¦ Manitoba Centre for Health policy (MCHP) ¦; %PUT ¦ MACRO: LINK ¦; %PUT ¦ JOB: Data Quality ¦; %PUT ¦ PROGRAMMER: Mahmoud Azimaee ¦; %PUT ¦ DATE: April 2011 ¦; %PUT ¦ Running for: &DOMAIN..&DB ... ; %PUT ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯; proc format; value $phintype '0'='0 MH verified against concurrent registries' '1'='1 MH redirected to this ScrPHIN from FILEPHIN' '2'='2 MCHP modified sibling''s ScrPHIN' '3'='3 MCHP assigned ScrPHIN from Registry' '4'='4 MCHP db specific ScrPHIN - No MH found' '5'='5 MCHP db specific ScrPHIN - personid not incl in crosswalk' '6'='6 Not Defined June 2010' '7'='7 HCN is not Manitoba Resident' '8'='8 Missing or unspecified' '9'='9 System, not individual ScrPHIN' ; run; %LET DOMAIN=%UPCASE(&DOMAIN); %LET DB=%UPCASE(&DB); %LET PHIN=%UPCASE(&PHIN); %LET TYPE=%UPCASE(&TYPE); %LET CLUSTER=%UPCASE(&CLUSTER); filename tempfile "/dq/saswork/&spdsuser/temp_data.dat"; /*Directory where temp_data.dat is saved*/ data _NULL_ ; rc=FDELETE ('tempfile'); run; ********************* IF DATA IS CLUSTERED *******************; %IF &CLUSTER=YES %THEN %DO; proc contents data=&DOMAIN..&DB out=contents; ods output EngineHost=clsmem; run; data clsmem; set clsmem; if label1='Cluster Members are' then do; call symput('CLSMEM',cvalue1); /* call symput('DSN', _N_ );*/ end; run; %LET DSN=%EVAL(%SYSFUNC(COUNTC("&CLSMEM",','))+1); %LET MEMBERS= ; %DO I=1 %TO &DSN; %LET TEMP=%SCAN("&CLSMEM",&I," , " ); %LET TEMP=%SCAN(&TEMP,1,"( " ); %IF &I=1 %THEN %LET MEMBERS=&TEMP; %ELSE %LET MEMBERS= &MEMBERS &TEMP; %END; %GOTO COMMON; %END; ***************************************************************; ****************** IF DATA IS NOT CLUSTERED *******************; PROC SQL NOPRINT; SELECT MEMNAME INTO :DSS SEPARATED BY " " FROM DICTIONARY.members WHERE LIBNAME = "&DOMAIN" AND SUBSTR(MEMNAME,1,%SYSFUNC(LENGTH(&DB)))="&DB"; quit; %LET DSN=%EVAL(%SYSFUNC(COUNTC(&DSS,' '))+1); %DO J=1 %TO &DSN; PROC CONTENTS DATA=&DOMAIN..%SCAN(&DSS,&J," ") OUT=contents&J NOPRINT; run; data contents; %IF &J=1 %THEN %DO; set contents&J; %END; %ELSE %DO; set contents contents&J; %END; run; %END; ***************************************************************; %COMMON: PROC SORT DATA=contents; by MEMNAME; run; Data contents; set contents; by MEMNAME; retain _PHIN_ ; if first.MEMNAME then do; nvar=0; _PHIN_ =0; end; nvar+1; if UPCASE(NAME)="&PHIN" then _PHIN_ =1; if last.MEMNAME & _PHIN_; keep LIBNAME MEMNAME MEMLABEL NAME NOBS NVAR _PHIN_ ; Label NVAR='# of Variables'; run; Data contents; Set contents; by MEMNAME; _N=_N_; run; %GETNOBS(contents); %IF &NO=0 %THEN %DO; %PUT ____________________________________________________________; %PUT ¦ NOTE: None of the selected tables had any PHIN variable. ¦; %PUT ¦ Macro will stop without generating any output ¦; %PUT ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯; filename tempfile "/dq/saswork/&spdsuser/&DOMAIN._&DB._LINKABILITY.xls"; /*Directory where &DOMAIN._&DB._LINKABILITY.xls is saved*/ data _NULL_ ; rc=FDELETE ('tempfile'); run; %GOTO ENDIT; %END; ****************** IF DATA IS NOT CLUSTERED *******************; %IF &CLUSTER=NO %THEN %DO; PROC SQL NOPRINT; SELECT MEMNAME INTO :DS SEPARATED BY " " FROM WORK.contents quit; %DO I=1 %TO &NO; proc freq data=&DOMAIN..%SCAN(&DSS,&I," ") noprint; table &TYPE / list NOCUM NOFREQ out=PHINTYPE&I; format &TYPE $phintype. ; run; data PHINTYPE&I; set PHINTYPE&I; rename percent=%SCAN(&DSS,&I," "); drop COUNT; label percent="%SCAN(&DSS,&I," ")"; run; Data temp; set &DOMAIN..%SCAN(&DSS,&I," ") (keep=&PHIN &TYPE); if &TYPE in ('0','1','2','3','6'); run; %GETNOBS(temp); %LET NO_Rec&I=&NO; PROC SORT DATA=temp; by &PHIN; run; Data temp; set temp; by &PHIN; if first.&PHIN; run; %GETNOBS(temp); %LET PT_Rec&I=&NO; %END; %END; ***************************************************************; ********************* IF DATA IS CLUSTERED *******************; %PUT &DSN; %IF &CLUSTER=YES %THEN %DO; %DO I=1 %TO &DSN; proc freq data=&DOMAIN..&DB (MEMNUM=&I) noprint; table &TYPE / list NOCUM NOFREQ out=PHINTYPE&I; format &TYPE $phintype. ; run; data PHINTYPE&I; set PHINTYPE&I; rename percent=%SCAN(&MEMBERS,&I," "); drop COUNT; label percent="%SCAN(&MEMBERS,&I," ")"; run; Data temp; set &DOMAIN..&DB (MEMNUM=&I keep=&PHIN &TYPE); run; %GETNOBS(temp); %LET NOBS&I=&NO; Data temp; set temp; if &TYPE in ('0','1','2','3','6'); run; %GETNOBS(temp); %LET NO_Rec&I=&NO; PROC SORT DATA=temp; by &PHIN; run; Data temp; set temp; by &PHIN; if first.&PHIN; run; %GETNOBS(temp); %LET PT_Rec&I=&NO; %END; data contents; set contents; *retain libname memname memlabel name _PHIN_ nvar; %DO J=1 %TO &DSN; %LET temp=%SCAN(&MEMBERS,&J," "); %PUT &MEMBERS; %PUT &J &TEMP; /* memname=memname|| SYMGETC('temp');*/ memname= SYMGETC('temp'); _N = J ; output; %END; run; %END; ********************* IF DATA IS NOT CLUSTERED *******************; %IF &CLUSTER=NO %THEN %DO; %GETNOBS(contents); data _null_ ; file "/dq/saswork/&spdsuser/temp_data.dat" ; /*Directory where temp_data.dat is saved */ %DO I=1 %TO &NO; PUT "&&NO_Rec&I" " " "&&PT_Rec&I"; %END; run; data temp; infile "/dq/saswork/&spdsuser/temp_data.dat" ; /*Directory where temp_data.dat is saved*/ input NO_Rec PT_Rec; _N=_N_; run; data contents; merge temp contents; by _N ; run; proc sql; create table linkability as select memname as Table ,memlabel as Lable ,nobs as Total_Records ,NO_Rec as Linkable_Records ,100*NO_Rec/nobs as Percentage_Linkable_Records ,PT_Rec as Linkable_Individuals from contents; quit; %END; ********************* IF DATA IS CLUSTERED *******************; %IF &CLUSTER=YES %THEN %DO; %LET NO=&DSN; data _null_ ; file "/dq/saswork/&spdsuser/temp_data.dat" ; /*Directory where temp_data.dat is saved*/ %DO I=1 %TO &NO; %LET M=%SCAN(&MEMBERS,&I," "); PUT "&&DOMAIN" " " "&&M" " " "&&NOBS&I" " " "&&NO_Rec&I" " " "&&PT_Rec&I"; %END; run; data temp; length LIBNAME MEMNAME $ 30 ; infile "/dq/saswork/&spdsuser/temp_data.dat" ; /*Directory where temp_data.dat is saved*/ input LIBNAME $ MEMNAME $ NOBS NO_Rec PT_Rec; _N=_N_; run; data contents; set temp; by _N ; run; proc sql; create table linkability as select memname as Table ,nobs as Total_Records ,NO_Rec as Linkable_Records ,100*NO_Rec/nobs as Percentage_Linkable_Records ,PT_Rec as Linkable_Individuals from contents; quit; %END; **********************************************************; %LET I=%EVAL(&I-1); data PHINTYPE; merge PHINTYPE1-PHINTYPE&I; by &TYPE; run; Proc Print data=linkability; run; Proc Print data=PHINTYPE; run; data PHINTYPE0; length &TYPE $ 40; set PHINTYPE; &TYPE=put(&TYPE,$phintype.); run; filename tempfile "/dq/saswork/&spdsuser/&DOMAIN._&DB._PHINTYPE.xls"; /*Directory where &DOMAIN._&DB._PHINTYPE.xls is saved*/ data _NULL_ ; rc=FDELETE ('tempfile'); run; filename tempfile "/dq/saswork/&spdsuser/&DOMAIN._&DB._LINKABILITY.xls"; /*Directory where &DOMAIN._&DB._LINKABILITY.xls is saved*/ data _NULL_ ; rc=FDELETE ('tempfile'); run; PROC EXPORT DATA= WORK.linkability OUTFILE= "/dq/saswork/&spdsuser/&DOMAIN._&DB._LINKABILITY.xls" /*Directory where &DOMAIN._&DB._LINKABILITY.xls is saved*/ DBMS=XLS REPLACE; run; PROC EXPORT DATA= WORK.PHINTYPE0 OUTFILE= "/dq/saswork/&spdsuser/&DOMAIN._&DB._PHINTYPE.xls" /*Directory where &DOMAIN._&DB._PHINTYPE.xls is saved*/ DBMS=XLS REPLACE; run; PROC DATASETS lib=WORK; delete contents1-contents&J temp contents PHINTYPE0-PHINTYPE&I PHINTYPE; quit;run; filename tempfile "/dq/saswork/&spdsuser/temp_data.dat"; /*Directory where temp_data.dat is saved*/ data _NULL_ ; rc=FDELETE ('tempfile'); run; %ENDIT: %MEND LINK;