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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.