Data Step Question

[c8826024]

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.                                                                                     

[art297]

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;

[c8826024]

But some may have the treatment every day, not necessarily 3 days.

[art297]

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;

[Linlin]

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;

[c8826024]

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.                     

[art297]

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;

[Linlin]

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;