Contributor
Posts: 38

# Data Step Question

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.

PROC Star
Posts: 8,167

## Data Step Question

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;

Contributor
Posts: 38

## Data Step Question

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

PROC Star
Posts: 8,167

## Data Step Question

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;

Super Contributor
Posts: 1,636

## Re: Data Step Question

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;

Contributor
Posts: 38

## Re: Data Step Question

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.

PROC Star
Posts: 8,167

## Re: Data Step Question

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;

Super Contributor
Posts: 1,636

## Re: Data Step Question

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;

Discussion stats
• 7 replies
• 212 views
• 0 likes
• 3 in conversation