topic Re: delete rows and changing values in SAS Procedures

delete rows and changing values

starz4ever2007 — Thu, 11 Aug 2016 18:23:45 GMT

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

Re: delete rows and changing values

Astounding — Thu, 11 Aug 2016 19:36:56 GMT

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;

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.

Re: delete rows and changing values

starz4ever2007 — Thu, 11 Aug 2016 21:16:16 GMT

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.

Re: delete rows and changing values

Astounding — Fri, 12 Aug 2016 03:28:23 GMT

Re: delete rows and changing values

Astounding — Fri, 12 Aug 2016 03:31:34 GMT

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;

Re: delete rows and changing values

starz4ever2007 — Fri, 12 Aug 2016 04:05:58 GMT

worked perfectly! thank you 🙂