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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.