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

I have the following data set that I would like to melt and reshape by aggregating 1) the total number of days on drugs where it was taken >=7 consecutive days 2) the drug amount they took.

 

Source data:

Patient     Drug      StartDate        EndDate  amountperday   unit                                 
A             E      2016-02-20     2016-02-28       1         mg                                                        
A             G      2016-03-20     2016-03-27       2         mg
A             F      2016-02-20     2016-02-21       7         mg  
A             E      2016-02-20                      4         mg 
B             E      2016-01-05     2016-01-24       3         mg
B             G      2016-01-07     2016-01-14       5         mg 

Result:

Patient     Drug     totamount unit   Days  StartDate   EndDate                               
A           EG       22        mg      15    2016-02-20  2016-03-27                                                
B           EG       95        mg      27    2016-01-05  2016-01-24

For A the 22 was calculated 1(8days) + 2(7days).
For A the 15 was calculated as 8 days + 7 days.
For B the 95 was calculated as 3(20days) + 5(7days).
For B the 27 was calculated as 20 days + 14 days - 7 overlapping days.

 

My attempt at this would be the following: 

 

data calculation; 

set mydata; 

days=datdif(startdate, enddate, 'act/act');

[I would have to mark the overlapping days but I'm not sure how I would do that and then add a by statement]

if days >=7 then dose = amountperday*days;

else = . ; 

run; 

 

Please let me know how I can get my desired outcome. It does not have to use my attempt. 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ScottBass
Rhodochrosite | Level 12

I don't have SAS in front of me over the weekend, so I can only describe this approach in words and untested code.

 

1) Filter out those records where StartDate to EndDate is < 7 days.

2) Use a data step to transpose the data, with one record per day.  Example code (untested):

 

data transposed;
   set have;
   where datdif(StartDate,EndDate,'act/act') >= 7;
   do date=StartDate to EndDate;
      output;
   end;
   drop i;
format date date9.; run;

3) Use first. to keep only one record with a given date:

 

proc sort data=transposed out=sorted;
   by patient date drug;
run;

data no_overlaps;
   set sorted;
   by patient date drug;
   if first.date;
run;

* then count the number of rows to get the number of days minus overlapping days ;

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

View solution in original post

2 REPLIES 2
ScottBass
Rhodochrosite | Level 12

I don't have SAS in front of me over the weekend, so I can only describe this approach in words and untested code.

 

1) Filter out those records where StartDate to EndDate is < 7 days.

2) Use a data step to transpose the data, with one record per day.  Example code (untested):

 

data transposed;
   set have;
   where datdif(StartDate,EndDate,'act/act') >= 7;
   do date=StartDate to EndDate;
      output;
   end;
   drop i;
format date date9.; run;

3) Use first. to keep only one record with a given date:

 

proc sort data=transposed out=sorted;
   by patient date drug;
run;

data no_overlaps;
   set sorted;
   by patient date drug;
   if first.date;
run;

* then count the number of rows to get the number of days minus overlapping days ;

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Ksharp
Super User

Honestly, I can't understand your question completely .

 

data have;
input Patient   $  Drug   $   StartDate : yymmdd10.       EndDate : yymmdd10. amountperday   unit  $;
format StartDate  EndDate : yymmdd10.;
cards; 
A             E      2016-02-20     2016-02-28       1         mg                                                        
A             G      2016-03-20     2016-03-27       2         mg
A             F      2016-02-20     2016-02-21       7         mg  
A             E      2016-02-20        .              4         mg 
B             E      2016-01-05     2016-01-24       3         mg
B             G      2016-01-07     2016-01-14       5         mg 
;
run;
data temp;
 set have;
 if enddate-startdate>=7;
run;
data want;
   length new_drug $ 100;
   totalamount=0;
   days=0;
 do until(last.patient);
   set temp;
   by patient;
   if first.patient then new_startdate=startdate;
   new_drug=cats(new_drug,drug);
   totalamount+	(enddate-startdate)*amountperday;
   days+(enddate-startdate);
 end;
 new_enddate=enddate;
 format new_startdate new_enddate yymmdd10.;
 keep new_: patient unit totalamount days;
 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
  • 2 replies
  • 2163 views
  • 0 likes
  • 3 in conversation