Help using Base SAS procedures

delete rows and changing values

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

delete rows and changing values

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     .........


Accepted Solutions
Solution
‎08-12-2016 12:05 AM
Super User
Posts: 5,255

Re: delete rows and changing values

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;

View solution in original post


All Replies
Super User
Posts: 5,255

Re: delete rows and changing values

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.

Contributor
Posts: 45

Re: delete rows and changing values

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.

Super User
Posts: 5,255

Re: delete rows and changing values

 
Solution
‎08-12-2016 12:05 AM
Super User
Posts: 5,255

Re: delete rows and changing values

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;

Contributor
Posts: 45

Re: delete rows and changing values

worked perfectly! thank you Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 394 views
  • 1 like
  • 2 in conversation