DATA Step, Macro, Functions and more

Data Step Question

Reply
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: 7,492

Data Step Question

Here is one way:

data want (keep=id daysSmiley Happy;

  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: 7,492

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 daysSmiley Happy;

  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: 7,492

Re: Data Step Question

So, you actually have date1 to date120 and dosage1 to dosage120?  How about:

data want (keep=id hospital_dt daysSmiley Happy;

  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

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;

Ask a Question
Discussion stats
  • 7 replies
  • 197 views
  • 0 likes
  • 3 in conversation