topic Re: Data Step Question in SAS Programming

Data Step Question

c8826024 — Mon, 28 Nov 2011 19:59:29 GMT

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

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.

Data Step Question

art297 — Mon, 28 Nov 2011 21:43:11 GMT

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;

Data Step Question

c8826024 — Mon, 28 Nov 2011 22:26:42 GMT

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

Data Step Question

art297 — Mon, 28 Nov 2011 22:49:34 GMT

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;

Re: Data Step Question

Linlin — Tue, 29 Nov 2011 13:22:43 GMT

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

;

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;

Re: Data Step Question

c8826024 — Wed, 30 Nov 2011 18:12:58 GMT

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.

Re: Data Step Question

art297 — Wed, 30 Nov 2011 18:45:35 GMT

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;

Re: Data Step Question

Linlin — Wed, 30 Nov 2011 19:22:07 GMT

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;