hi!
i want to create a variable dose_90 by adding the doses of each row within 90 days from each Index_Date by ID.
For example, when Index_Date is September 27, 2001 as shown in the figure below, among other index_dates of the ID, the dose( 20mg) is added to 10mg of the existing row to yield 30mg.
data have;
input id date1 :yymmdd10. dose;
format date1 yymmdd10. ;
cards;
1 20010927 10
1 20011003 20
1 20020204 100
2 20010314 10
2 20010426 50
2 20010509 25
;
How about using PROC SQL to join the table with itself?
data have;
input id date1 :yymmdd10. dose;
format date1 yymmdd10. ;
cards;
1 20010927 10
1 20011003 20
1 20020204 100
2 20010314 10
2 20010426 50
2 20010509 25
;
proc sql;
create table want as
select a.id, a.date1, a.dose
, sum(b.dose) as total
from have a
inner join have b
on a.id = b.id
and b.date1 between a.date1 and a.date1+90
group by a.id,a.date1,a.dose
order by id,date1
;
quit;
proc print;
run;
Obs id date1 dose total 1 1 2001-09-27 10 30 2 1 2001-10-03 20 20 3 1 2002-02-04 100 100 4 2 2001-03-14 10 85 5 2 2001-04-26 50 75 6 2 2001-05-09 25 25
How about using PROC SQL to join the table with itself?
data have;
input id date1 :yymmdd10. dose;
format date1 yymmdd10. ;
cards;
1 20010927 10
1 20011003 20
1 20020204 100
2 20010314 10
2 20010426 50
2 20010509 25
;
proc sql;
create table want as
select a.id, a.date1, a.dose
, sum(b.dose) as total
from have a
inner join have b
on a.id = b.id
and b.date1 between a.date1 and a.date1+90
group by a.id,a.date1,a.dose
order by id,date1
;
quit;
proc print;
run;
Obs id date1 dose total 1 1 2001-09-27 10 30 2 1 2001-10-03 20 20 3 1 2002-02-04 100 100 4 2 2001-03-14 10 85 5 2 2001-04-26 50 75 6 2 2001-05-09 25 25
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.