Union join Proc Sql in Macro with select case when statements for Probabilistic Record Linkage

[atzamis]

Hi,

I know it is not the most elegant solution but it seems to work  :

data mydata_clean;

   input SEX $ DOB PFI ZIP RACE;

datalines;

F     189804      125   10111 1

M     189802      126   12365 2

F     189801      129   12369 1

M     190111      256   14236 2

F     190302      101   12144 1

M     190307      115   12203 2

M     190307      115   12203 2

M     189806      356   18963 1

F     190308      258   14789 2

F     190302      101   12144 1

;

run;

%macro sqq(myvarn);

PROC SQL ;

CREATE TABLE SAMPLE2 AS 

SELECT * FROM mydata_clean WHERE  &myvarn. ="F";

QUIT;

%mend;

DATA _NULL_;

CALL EXECUTE('%nrstr(%sqq (sex ));');

RUN;

[Cruise]

Thanks, the code works but not for the purpose I'm trying to accomplish here.

[Cruise]

Hi Reeza, I tried The Link King. This software specifically requires first and lastnames which my data doesn't have.

[Cruise]

Hi guys,

Thank you very much for all your suggestions. I gave up on macro and instead went around the problem with early threshold. Ideally, i would have taken the intersect of data resulting from each program using 5 different blocking variables using macro.

data threshold; set similarity_score;
if score =>17 then output threshold; run;  

But instead, I truncated data in data step as shown above using conservative value for cut-off that made following translink program doable to my RAM.

[Cruise]

Below is macro that finally worked out, if someone would want to look at or use.

%macro blocking(myvar,data);
proc sql;
create table sel.&data as
select 
 a.obs_num    as obs_num_1,  b.obs_num  as obs_num_2, 
 a.sex        as sex_1,      b.sex      as sex_2, 
 a.birthy     as birthy_1,   b.birthy   as birthy_2,
 a.birthm     as birthm_1,   b.birthm   as birthm_2,
 a.zip        as zip_1,      b.zip      as zip_2,
 a.race       as race_1,     b.race     as race_2,
 a.ethnic     as ethnic_1,   b.ethnic   as ethnic_2,

case when a.sex = b.sex            then 1.0  else -8.0 end as sex_score,
case when a.birthy = b.birthy      then 8.8  else -4.3 end as by_score,
case when a.birthm = b.birthm      then 5.5  else -4.3 end as bm_score,
case when a.zip = b.zip            then 8.7  else -4.3 end as zip_score,
case when a.race = b.race          then 3.5  else -4.2 end as race_score,
case when a.ethnic = b.ethnic      then 0.02 else -0.4 end as eth_score,

calculated sex_score + calculated by_score + calculated bm_score + calculated zip_score+ calculated race_score+ calculated eth_score
as score
from sel.ordata as a INNER JOIN sel.ordata as b
on a.obs_num > b.obs_num and a.&myvar=b.&myvar 
where a.&myvar is not missing and calculated score ge 20;
;
quit;
%mend blocking;
%blocking(birthy,byblock1);
%blocking(birthm,bmblock1);