BookmarkSubscribeRSS Feed
Sujithpeta
Quartz | Level 8

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 

2 REPLIES 2
mkeintz
PROC Star

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?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User

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;
 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 474 views
  • 0 likes
  • 3 in conversation