delete rows and changing values

[starz4ever2007]

dataset:

subj        drug           week               q1      q2      q3 ... qtotal

1              a                  1                   1         3        4       10

1              a                  2                   1         0        4       0

1              a                  3                   0         3        0       0

1              a                  4                   0         3        4       0

1              a                  16                 0         3        0       0            

2              d                  1                   1         3        1       0

2              d                  2                   1         2        4       0

2              d                  3                   0         3        4       0

2              d                  16                 0         0        0       0

3              a                  1                   1    .......

3              a                  2                   2    .......

3              a                 16                  4     ....... 

4              b                  1                  3      .........

4              b                  2                  3     .........

4              b                  3                  1..........

5              c                   1                  3

There a couple things I would like to do with this data set. I would like to create a new data set where it would delete for each subject all rows except where q1 reaches 0 by the earliest week (for example, for subj 1, all rows should be deleted except for the row with the value 3 in the week variable (since it was the first (earliest) week that q1 turned to 0. For subj 2, again all rows except the row with the value 4 in the week variable should be deleted).  

And is there any way to keep in the subjects that didn't make it to 0 by week 16 and put their q1 value as 1 (see subj 3)....and if they didnt have a week 16 then to use whatever week has the most closest available value (to Week 16) and put 1 as the value in q1. (see subj  4)

new dataset looks like 

subj        drug           week               q1      q2      q3 ... qtotal

1              a                  3                   0         3        0       0

2              d                  3                   0         3        4       0

3              a                 16                  1     ..........

4              b                 3                    1     .........

[Astounding]

I'm going to make two assumptions here.  First, your data must be in sorted order BY SUBJ WEEK.  And second, the largest WEEK value that the program would encounter is 16.  (If that's not the case, some of the rules about "closest to 16" need to be clarified.)

Here's one way to approach the problem:

data want;

do until (last.subj);

   set have;

   by subj week;

   if save_week=. and q1=0 then save_week=q1;

end;

if save_week=. then save_week=week;

do until (last.subj);

   set have;

   by subj;

   if week=save_week then do;

      if q1 ne 0 then q1=1;

      output;

   end;

end;

drop save_week;

run;

It's untested code ... let's see how close you get with it.

The top loop reads through all the records for a subject, and locates the week that should be kept.  The bottom loop reads through those same records to extract the proper week.

[starz4ever2007]

it gave all the value of q1 as 1, when there should be 0 if q1 reached 0 at a given week. it also deleted subjects that had 0 prior to week 16.

[Astounding · Accepted solution]

Sorry, this statement is the culprit:

if save_week=. and q1=0 then save_week=q1;

It should read:

if save_week=. and q1=0 then save_week=week;

[starz4ever2007]

worked perfectly! thank you 🙂