Hi,
I have a dataset like this:
id date1 date2 date3 dosage1 dosage2 dosage3
1 1/2 1/5 10 20
2 1/10 30
3 1/2 1/5 1/31 10 20 30
4
The final dataset I wants looks like this:
id cumdosage1 cumdosage2 cumdosage3.... cumdosage5.... cumdosage10.... cumdosage31
1 0 10 10 30 30 30
2 30 30 30 30
3 0 0 0 0 10 10
4 0 0 0 0 0 0
So, cumdosage1 - cumdosage31 are corresponding to total days of the month and will be the cumulative dosages month-to-date.
Do you know how I can re-construct my dataset?
Thanks.
Here is one way:
data want (keep=id days:);
set have;
array days(31);
array dates(*) date1-date3;
array dosages(*) dosage1-dosage3;
do i=1 to 31;
days(i)=0;
end;
do i=1 to 3;
if not missing(dates(i)) then
days(day(dates(i)))=dosages(i);
end;
do i=2 to 31;
days(i)=sum(days(i),days(i-1));
end;
run;
But some may have the treatment every day, not necessarily 3 days.
If you mean that your input file really has date1 to date31 and dosage1 to dosage31, then you would only have to change the code as follows:
data want (keep=id days:);
set have;
array days(31);
array dates(*) date1-date31;
array dosages(*) dosage1-dosage31;
do i=1 to 31;
days(i)=0;
end;
do i=1 to 31;
if not missing(dates(i)) then
days(day(dates(i)))=dosages(i);
end;
do i=2 to 31;
days(i)=sum(days(i),days(i-1));
end;
run;
data have;
infile cards missover delimiter=',' ;
input id $1. (date1-date3)( :$4.) dosage1 dosage2 dosage3;
cards;
1, 1/2, 1/5, , 10, 20, .
2, 1/10, , , 30, ., .
3, 1/2, 1/5, 1/31, 10, 20, 30
4
;
run;
data want(keep=id cum: );
set have;
array ds(31);
array cum_dose(31);
array dates(*) date:;
array dosages(*) dosage:;
do i=1 to 31;
ds(i)=0 ;
cum_dose(i)=0;
end;
do i=1 to dim(dates);
j=input(scan(dates(i),2),2.);
if j then ds(J)=dosages(i);
end;
cum_dose(1)=ds(1);
do i=2 to 31;
cum_dose(i)=cum_dose(i-1)+ds(i);
end;
run;
The solution is elegant.
But if I have additional variable, called hospital_date, which is date when the patience first admitted into the hospital. I want to know 120 days's (not a month) treatment for each patience:
id date1 date2 date3 dosage1 dosage2 dosage3 hospital_dt
1 1/2 1/5 10 20 12/1/2010
2 1/10 30 11/1/2010
3 1/2 1/5 1/31 10 20 30 1/1/2011
4 4/1/2011
I try to modify your code but it is not working.
Thanks.
So, you actually have date1 to date120 and dosage1 to dosage120? How about:
data want (keep=id hospital_dt days:);
set have;
array days(120);
array dates(*) date1-date120;
array dosages(*) dosage1-dosage120;
do i=1 to 120;
days(i)=0;
end;
do i=1 to 120;
if not missing(dates(i)) then
days(dates(i)-hospital_dt+1)=dosages(i);
end;
do i=2 to 120;
days(i)=sum(days(i),days(i-1));
end;
run;
how about:
data have;
infile cards missover delimiter=',' ;
informat hospital_dt mmddyy10.;
format hospital_dt mmddyy10.;
input id $1. (date1-date3)( :$4.) dosage1 dosage2 dosage3 hospital_dt;
cards;
1, 1/2, 1/5, , 10, 20, .,12/1/2010
2, 1/10, , , 30, ., . ,11/1/2010
3, 1/2, 1/5, 1/31, 10, 20, 30,1/1/2011
4, , , ,.,.,.,4/1/2011
;
run;
data want(keep=id cum: );
set have;
array ds(120);
array cum_dose(120);
array dates(*) date:;
array dosages(*) dosage:;
do i=1 to 120;
ds(i)=0 ;
cum_dose(i)=0;
end;
do i=1 to dim(dates);
j=mdy(input(scan(dates(i),1),2.),input(scan(dates(i),2),2.),2011)-hospital_dt+1;
if j>0 and j le 120 then ds(J)=dosages(i);
end;
cum_dose(1)=ds(1);
do i=2 to 120;
cum_dose(i)=cum_dose(i-1)+ds(i);
end;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.