Hello,
I'm working with a longitudinal data set, and am trying to identify drugs administered to a patient. However, I'm only interested in patients who had no subsequent drugs prescriptions given. (i.e. if a patient was given drugs on date 12/26/2010, and on 12/27/2010 I want to drop them).
Data have;
input Medical_Record_Number Generic_Drug date_of_Service;
datalines;
9512028 112201 12/29/2010
9512028 146435 12/29/2010
9512028 112201 12/30/2010
9512028 146435 12/30/2010
9512028 146455 12/30/2010
9512028 131301 12/30/2010
9512028 112201 12/30/2010
9512028 117155 12/30/2010
9512028 115235 12/30/2010
9512028 115325 12/30/2010
9612028 115325 11/30/2010
9612028 115325 11/30/2010
9612028 146451 11/30/2010
9612028 115325 11/30/2010
17942028 146435 01/09/2011
17942028 146455 01/10/2011
17942028 112201 01/10/2011
17942028 112201 01/10/2011
17942028 146451 01/10/2011
17942028 112131 01/10/2011
17942028 146435 01/10/2011
17942028 147421 01/10/2011
17942028 181211 01/10/2011
17942028 117155 01/10/2011
17942028 115235 01/10/2011
17942028 131141 01/10/2011
17942028 115325 01/10/2011
17942028 146451 01/10/2011
17942028 119215 01/10/2011
17942028 124143 01/11/2011
17942028 146455 01/12/2011
25132028 112201 01/17/2011
25132028 146218 01/17/2011
25132028 146435 01/17/2011
25132028 146455 01/18/2011
25132028 112131 01/18/2011
25132028 112201 01/18/2011
25132028 146435 01/18/2011
25132028 147421 01/18/2011
25132028 115130 01/18/2011
25132028 115325 01/18/2011
;
run;
From the above sample data I want to retain the following observations since that patient only had drugs administered on one day but nothing before or after:
9612028 115325 11/30/2010
9612028 115325 11/30/2010
9612028 146451 11/30/2010
9612028 115325 11/30/2010
I initially tried using _keepflag, but I don't think that's really what I want. Any suggestions?
Sincerely,
A newer SAS user 🙂
SQL is absolutely a dream for problems like this. You can take a look at the intermediate datasets to see how the process works.
Tom
proc sql noprint;
create table Inter01 as
select Medical_Record_Number, date_of_Service, count(*) as DateCount
from have
group by Medical_Record_Number, date_of_Service;
create table Inter02 as
select Medical_Record_Number, count(*) as RecCount
from Inter01
group by Medical_Record_Number
having RecCount = 1;
create table Want as
select h.* from Have h inner join Inter02 i
on h.Medical_Record_Number = i.Medical_Record_Number;
quit;
SQL is absolutely a dream for problems like this. You can take a look at the intermediate datasets to see how the process works.
Tom
proc sql noprint;
create table Inter01 as
select Medical_Record_Number, date_of_Service, count(*) as DateCount
from have
group by Medical_Record_Number, date_of_Service;
create table Inter02 as
select Medical_Record_Number, count(*) as RecCount
from Inter01
group by Medical_Record_Number
having RecCount = 1;
create table Want as
select h.* from Have h inner join Inter02 i
on h.Medical_Record_Number = i.Medical_Record_Number;
quit;
Thanks Tom! Super useful indeed, looks like I need to add SQL to my list of things to work on. 🙂
I hope you enjoy your endeavours with SAS. I've been using it the 1970's, and I STILL haven't got to the end of the things I have to learn!
Tom
@Shad what if the two dates were 12/26/2010, and on 12/28/2010? Would you still drop them?
For my purposes yes. I wanted to identify only individuals that received prescriptions on day 1 and at no other point.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.