BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Shad
Obsidian | Level 7

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 🙂 

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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;

View solution in original post

5 REPLIES 5
TomKari
Onyx | Level 15

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
Obsidian | Level 7

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

TomKari
Onyx | Level 15

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
Tourmaline | Level 20

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

Shad
Obsidian | Level 7

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 5 replies
  • 1230 views
  • 2 likes
  • 3 in conversation