topic Closest date matching without duplicates in New SAS User

Closest date matching without duplicates

Sujithpeta — Fri, 10 Jan 2020 20:48:00 GMT

I've two data sets with patient drug claim date and administration date, I want to map the drug to administration date without duplication of administration date (i.e. one administration date should only be for one drug date) and the hierarchy is to match the admin date happening on the same day as drug date as it's more reliable. If there is no same days admin date then find the closest date but making sure already matched admin dates aren't used again.

Have:

Patient DrugDate

A 01/05/2017

A 01/18/2017

A 01/31/2017

A 02/28/2017

Patient AdminDate

A 01/31/2017

A 02/28/2017

Want:

Patient DrugDate AdminDate

A 01/05/2017

A 01/18/2017

A 01/31/2017 01/31/2017

A 02/28/2017 03/17/2017

Appreciate your help. Thanks

Re: Closest date matching without duplicates

mkeintz — Fri, 10 Jan 2020 21:40:18 GMT

What is your rule if you have 2 consecutive drug dates that are unmatched, and you have an admin date exactly half-way between them?

Re: Closest date matching without duplicates

Ksharp — Sat, 11 Jan 2020 11:35:50 GMT

As Mike said there are too many scenario you need consider about .

data drug;
input Patient $  DrugDate : mmddyy10.;
format DrugDate mmddyy10.;
cards;
A          01/05/2017     
A          01/18/2017
A          01/31/2017   
A          02/28/2017  
;
 
data admin;
input Patient $  AdminDate : mmddyy10.;
format AdminDate mmddyy10.;
cards;
A          01/31/2017   
A          03/17/2017  
;

data temp;
set drug(in=ina) admin(rename=(AdminDate=DrugDate) in=inb);
by Patient DrugDate;
a=ina;b=inb;
run;
data want;
 merge temp temp(firstobs=2 keep=Patient DrugDate b
 rename=(Patient=_p DrugDate=_d b=_b));
if Patient=_p and a and _b then AdminDate=_d;
if a;
format AdminDate mmddyy10.;
keep Patient DrugDate AdminDate;
run;