/*----------------------------------------------------------------------------- File: ExamineDedupKeyValue.sas Author: John Sabel Washington State Education Research & Data Center Creation date: 5/2/11 Version: 0.91 Purpose You have a dataset that has been deduplicated twice, by different methods. This macro will compare those two methods for a single duplication ID that comes from one of the methods. A commented out example is available at the bottom of the file. It contains sample data and a call to the macro. Parameters DedupKeyValue_1: The value of the deduplication ID that you want to examine. DedupKeyName_1: Name of the the 1st method's duduplication key. DedupKeyName_2: Name of the the 2nd method's duduplication key. DsnIn: One or two-level name of the dataset that contains both methods deduplication IDs. Debug: (Optional) Y = Don't delete intermidiate datasets. N = Delete intermidiate datasets. Default is N. -----------------------------------------------------------------------------*/ %macro ExamineDedupKeyValue(DedupKeyValue_1=, DedupKeyName_1=, DedupKeyName_2=, DsnIn=, Debug=N); %put --- Start of %upcase(&sysmacroname) macro; %local Value; /* This section of code allows DedupKeyName_1 to be either a num field or a char field. */ proc sql noprint; /* Because DsnIn might be a two-level name, first create the empty dataset __TEMP as a one level name. */ CREATE TABLE __TEMP LIKE &DsnIn. ; /* Determine if DedupKeyName_1 is a Num or Char field from empty dataset __TEMP. */ SELECT UPCASE(TYPE) INTO :NumOrChar FROM DICTIONARY.COLUMNS WHERE UPCASE(LIBNAME) = 'WORK' AND UPCASE(MEMNAME) = '__TEMP' AND UPCASE(NAME) = "%upcase(&DedupKeyName_1.)" ; quit; %if &NumOrChar.=NUM %then %let Value=&DedupKeyValue_1.; %else %let Value="&DedupKeyValue_1."; proc sql; CREATE TABLE __DiverganceInstance AS SELECT * FROM &DsnIn. WHERE &DedupKeyName_1. = &Value. ; quit; proc sql; title1 "&DedupKeyName_1. = &DedupKeyValue_1."; SELECT 1 as OneOrTwo, __DiverganceInstance.* FROM __DiverganceInstance UNION SELECT 2, * FROM &DsnIn. WHERE &DedupKeyName_2. IN ( SELECT &DedupKeyName_2. FROM __DiverganceInstance ) ORDER BY OneOrTwo, &DedupKeyName_2. ; quit; title1; %if &Debug.=N %then %do; proc sql; DROP TABLE __TEMP, __DiverganceInstance; quit; %end; %put --- End of %upcase(&sysmacroname) macro; %mend ExamineKeyDedupValue; /* data SampleDedupData; input AlphaDedupId $ BetaDedupId ID FirstName $ DOB $; datalines; A100 111 1 John 07/20/65 A100 211 2 John 30/07/65 A200 211 3 Jack 30/07/65 A200 311 4 Jack 07/30/64 A300 311 5 James 07/30/64 A400 411 6 Jen 11/03/68 A400 511 7 Jen 11/03/02 A500 511 8 Jill 11/03/02 A500 611 9 Jill 11/01/99 ; run; %ExamineDedupKeyValue(DedupKeyValue_1=A100, DedupKeyName_1=AlphaDedupId, DedupKeyName_2=BetaDedupId, DsnIn=SampleDedupData, Debug=N) %ExamineDedupKeyValue(DedupKeyValue_1=511, DedupKeyName_1=BetaDedupId, DedupKeyName_2=AlphaDedupId, DsnIn=SampleDedupData, Debug=N) */