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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

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
  • 2 replies
  • 1504 views
  • 0 likes
  • 3 in conversation