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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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