Longitudinal data management - keeping only records based off dates

[Shad]

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 🙂 

[TomKari · Accepted solution]

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;

[Shad]

Thanks Tom! Super useful indeed, looks like I need to add SQL to my list of things to work on.  🙂 

[TomKari]

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

[PeterClemmensen]

@Shad what if the two dates were 12/26/2010, and on 12/28/2010? Would you still drop them?

[Shad]

For my purposes yes. I wanted to identify only individuals that received prescriptions on day 1 and at no other point.