BookmarkSubscribeRSS Feed
riya275
Obsidian | Level 7

Hi ,

 

I have data that looks like - 

IDDrugEpisodestart_dateend_date
1A11-Jan30-Mar
1A21-May3-Jul
1A328-Sep28-Oct
1A41-Nov30-Nov
1B11-Apr10-May
1B22-Nov28-Dec
1C11-Jul2-Aug

 

data abc;
input ID $ drug $ episode start_date date9. end_date date9.;
format start_date end_date date9.;
informat start_date end_date date9.;
datalines ;
1 A 1 01Jan2012 30Mar2012
1 A 2 01May2012 03Jul2012
1 A 3 28Sep2012 28Oct2012
1 A 4 01Nov2012 30Dec2012
1 B 1 01Apr2012 10May2012
1 B 2 02Nov2012 28Dec2012
1 B 3 01Jan2012 30Mar2012
1 C 1 01Jul2012 02Aug2012
;
run;

 

Here we have subjects and the the drugs they take. A new episode of one drug means that the person discontinued. 

If the start date ( start date of 1st episode) of second drug consumed , lies in between the episodes of first drug , then we will ignore all the further episodes of 1st drug.

Eg. here 1 april ( start date of drug B) lies after the first episode of drug A, so episode 2,3,4 of drug A would be deleted.

Similarly the start date for drug C lies after the end date of episode 1 for drug B then episode 2 of drug B would be deleted. 

 

The maximum number of episodes a subject can have is 15.

 

The resultant dataset should look like - 

IDDrugEpisodestart_dateend_date
1A11-Jan30-Mar
1B11-Apr10-May
1C11-Jul2-Aug
     
     
     
     

 

 

3 REPLIES 3
andreas_lds
Jade | Level 19

Can you please post what you expect as result? And please post data in usable form, especially the date-variables are important, because a date is not a date if the year is missing.

Kurt_Bremser
Super User

Shouldn't we have two observations for drug B as the last observation of B ends (30mar2012) before the start of C?

 

Anyway, this is what I come up with:

data abc;
input ID $ drug $ episode start_date :date9. end_date :date9.;
format start_date end_date date9.;
informat start_date end_date date9.;
datalines ;
1 A 1 01Jan2012 30Mar2012
1 A 2 01May2012 03Jul2012
1 A 3 28Sep2012 28Oct2012
1 A 4 01Nov2012 30Dec2012
1 B 1 01Apr2012 10May2012
1 B 2 02Nov2012 28Dec2012
1 B 3 01Jan2012 30Mar2012
1 C 1 01Jul2012 02Aug2012
;

data firsts (keep=id count start_date rename=(start_date=first_date));
set abc;
retain count;
by id drug;
if first.id then count = 0;
if first.drug;
count + 1;
run;

data want;
set abc;
by id drug;
retain count;
if _n_ = 1
then do;
  declare hash f (dataset:"firsts");
  f.definekey("id","count");
  f.definedata("first_date");
  f.definedone();
  call missing(first_date);
end;
if first.id then count = 1;
if first.drug then count + 1;
rc = f.find();
put id= drug= count= rc= start_date first_date;
if rc = 0 and start_date > first_date then delete;
run;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2160 views
  • 0 likes
  • 3 in conversation