BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
Astounding
PROC Star

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
Quartz | Level 8

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
PROC Star

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
Quartz | Level 8

worked perfectly! thank you 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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