topic Re: Union join Proc Sql in Macro with select case when statements for Probabilistic Record Linkage in SAS Procedures

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

Cruise — Sun, 05 Mar 2017 19:54:27 GMT

Actual question:

How to use proc sql's select case/when statements with macro by modifying the code recipe in the reference by Glenn Wright? The code that I am trying to modify here will be found on the page #10 in the reference. I also enclosed my modified code that doesn't produce data "similarity_score".

What am I doing wrong here?

Reference: Glenn Wright, Probabilistic Record Linkage, see page #10.

http://www.wuss.org/proceedings11/Papers_Wright_G_76128.pdf

Background:

My objective is to conduct proabilistic record linkage to reduce the duplicated entries of patients in hospital discharge data (NY). In the absence of personal variables such as name, address, unique idnetity code, I use gender, date of birth, patient facility identifier, zip and race. The bottom line is that any rows in the data that match to each other in combination of these five variables are the most likely to represent the same person rather different. Vice versa. SAS version: 9.4 (TS1M3).

"Sample" data:

data sample;
   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;

Code modified from Glenn Wright: (please see reference attached)

data mydata_clean (index = (obs_num sex dob pfi zip racecat)); set sample;
obs_num = _n_;
run;

%macro blocking_var(myvar);
create table similarity_score(compress=yes) 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.dob        as dob_1,      b.dob as dob_2,
 a.pfi        as pfi_1,      b.pfi as pfi_2,
 a.zip        as zip_1,      b.zip as zip_2,
 a.racecat    as racecat_1,  b.racecat as racecat_2,

select 
case 
when a.sex = b.sex then 1
else -5.64 end as sex_score,

case 
when a.pfi = b.pfi then 7.6
else -4.3 end as pfi_score,

case 
when a.dob = b.dob then 3.5
else -4.2 end as dob_score,

case 
when a.racecat = b.racecat then 1
else -3.3 end as racecat_score

case 
when a.zip = b.zip then 10.6
else -4.3 end as zip_score

calculated sex_score + calculated pfi_score +
calculated dob_score + calculated racecat_score + calculated zip_score as score

from mydata_clean as a INNER JOIN mydata_clean as b
on a.obs_num > b.obs_num
and a.&myvar. = b.&myvar.
where a.&myvar. is not missing
and calculated score=<-10
%mend;

data threshold;
set similarity_scores;
where score =<-12;
run;

Reference:

Glenn Wright's Probabilistic Record Linkage in SAS®, offers excellent solution to address the problem almost identical to mine. Any hints highly appreciated.

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

Reeza — Sun, 05 Mar 2017 19:58:59 GMT

I might be missing something, but you defined the macro but never actually ran it.

There should be a statement somewhere that looks like:

Proc sql;
%block_var(BLOCKVAR);
quit;

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

Reeza — Sun, 05 Mar 2017 20:00:13 GMT

PS. Your first step should be getting the code to work exactly as defined before making changes to it.

Also, you can take a look at the The Link King which offers a tool to create linkages based on SAS code as well.

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

Cruise — Sun, 05 Mar 2017 20:34:16 GMT

Thanks Reeza,

Using software is not permitted in this project unfortunately. I tried taking in and out the proc sql and quit with many different variations, but still doesn't work. Wish I had sql experience but not. Please see edited code. What do you think?

proc sql;
%macro blocking_var(myvar);
create table similarity_score(compress=yes) 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.dob        as dob_1,      b.dob as dob_2,
 a.pfi        as pfi_1,      b.pfi as pfi_2,
 a.zip        as zip_1,      b.zip as zip_2,
 a.racecat    as racecat_1,  b.racecat as racecat_2,

select
case
when a.sex = b.sex then 1
else -5.64 end as sex_score,

case
when a.pfi = b.pfi then 7.6
else -4.3 end as pfi_score,

case
when a.dob = b.dob then 3.5
else -4.2 end as dob_score,

case
when a.racecat = b.racecat then 1
else -3.3 end as racecat_score

case
when a.zip = b.zip then 10.6
else -4.3 end as zip_score

calculated sex_score + calculated pfi_score +
calculated dob_score + calculated racecat_score + calculated zip_score as score

from mydata_clean as a INNER JOIN mydata_clean as b
on a.obs_num > b.obs_num
and a.&myvar. = b.&myvar.
where a.&myvar. is not missing
and calculated score=<-10
;
quit;
%mend;

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

Reeza — Sun, 05 Mar 2017 20:45:25 GMT

Remove the macro components, get it working and then turn it into a macro. As is you're trying to debug too many things. My code was an example of how to use the macro.

If you're unfamiliar with the structure of the code the paper the paper did have full examples. Make sure it's the same format.

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

PGStats — Sun, 05 Mar 2017 21:00:22 GMT

Your code has (at least) 2 syntax errors which might go unnoticed when run inside a macro:

1) the second select keyword shouldn't be there

2) there should be commas after as zip_score and as racecat_score

As @Reeza suggested, get the code to work outside the macro before you run it as a macro. It is much easier to debug that way.

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

Cruise — Sun, 05 Mar 2017 22:23:19 GMT

Thanks. Working as suggested.

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

Cruise — Sun, 05 Mar 2017 22:24:19 GMT

Thanks. Good catches indeed.

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

Cruise — Tue, 07 Mar 2017 02:00:33 GMT

Hi PG,

I had proc sql worked outside the macro. But it loses its function when i throw it into macro. I run it but nothing happens. No data set created. What am I doing wrong here? I appreciate your time.

%macro blocking_var(myvar);
proc sql; 
 create table similarity_scores (compress=yes) 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.dob        as dob_1,      b.dob as dob_2,
 a.pfi        as pfi_1,      b.pfi as pfi_2,
 a.zip        as zip_1,      b.zip as zip_2,
case when a.sex = b.sex then 2
     else -5 end as sex_score,
case when a.dob = b.dob then 12
     else -6 end as dob_score,
case when a.pfi = b.pfi then 10
     else -5 end as pfi_score,
case when a.zip = b.zip then 10
     else -5 end as zip_score,

calculated sex_score + calculated dob_score  + calculated pfi_score +calculated zip_score 
as total_score 
from
mydata_clean as a INNER JOIN mydata_clean as b  
 on a.obs_num > b.obs_num
 and a.&myvar.=b.&myvar.
 where a.&myvar. is not missing
 and calculated score <=-10  
;
quit;
%mend;

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

Reeza — Tue, 07 Mar 2017 02:22:31 GMT

Back to my first comment - where do you call/execute the macro?

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

Cruise — Tue, 07 Mar 2017 02:28:28 GMT

%macro blocking_var(myvar);
proc sql; 
 "union"
quit;
%mend;

Hi Reeza,

My last code posted included above macro elements, isn't it?

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

Reeza — Tue, 07 Mar 2017 02:35:01 GMT

No. That's a macro definition, you still need to execute the macro.

Macro definition

%macro print_demo(dataset_name);

proc print data=&dataset_name (obs=10);
run;

%mend;

Macro execution:

%print_demo(sashelp.cars);

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

Cruise — Tue, 07 Mar 2017 02:41:32 GMT

Like so?

%blocking_var(sex,dob,pfi,zip);

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

Cruise — Tue, 07 Mar 2017 02:50:46 GMT

I get:

ERROR: More positional parameters found than defined.

when I add:

%blocking_var(sex,dob,pfi,zip);

I also tried:

%blocking_var("sex","dob","pfi","zip");

still get the same error as above

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

Cruise — Wed, 08 Mar 2017 14:09:59 GMT

Hi ya'll,

Still working on it. I really have to get it working.

Now I have macro definition and executions parts and the error I get is:

%macro blocking_var(myvar);
proc sql; 
 create table similarity_scores (compress=yes) 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.dob        as dob_1,      b.dob as dob_2,
a.pfi        as pfi_1,      b.pfi as pfi_2,
a.zip        as zip_1,      b.zip as zip_2,
case when a.sex = b.sex then 2
     else -5 end as sex_score,
case when a.dob = b.dob then 12
     else -6 end as dob_score,
case when a.pfi = b.pfi then 10
     else -5 end as pfi_score,
case when a.zip = b.zip then 10
     else -5 end as zip_score,

calculated sex_score + calculated dob_score  + calculated pfi_score +calculated zip_score 
as total_score 
from
mydata_clean as a INNER JOIN mydata_clean as b  
 on a.obs_num > b.obs_num
and a.&myvar.=b.&myvar.
where a.&myvar. is not missing
and calculated score <=-10  
;
quit;
%mend blocking_var;
%blocking_var(sex); 
%blocking_var(dob);
%blocking_var(pfi);
%blocking_var(zip);

Any hints?

Thank you!!!

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

atzamis — Thu, 09 Mar 2017 19:26:33 GMT

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 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;

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

Cruise — Tue, 14 Mar 2017 22:35:57 GMT

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

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

Cruise — Tue, 14 Mar 2017 22:38:31 GMT

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

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

Cruise — Tue, 14 Mar 2017 22:44:02 GMT

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.

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

Cruise — Fri, 21 Apr 2017 03:43:03 GMT

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);