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.
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 ;
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 ;
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;
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!
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.
Ready to level-up your skills? Choose your own adventure.