DATA Step, Macro, Functions and more

Difference between values in two rows

Reply
Frequent Contributor
Posts: 84

Difference between values in two rows

Hi All,

 

I have a dataset with 20 variables and 16 subjects, but each subject has two rows since I have two timepoints (1 and 2) for each subject. I want to add a row for each subject which marked as timepoint 3, but the value is the difference of (timepoint 2 - timepoint 1). for example:

 

subject id  timepoint      lab1      lab2   lab3  lab4    lab5

1                1                   0.5       0.6     15     18       12

1                2                  0.4        0.6      12     20      18

1                3                  -0.1       0.0      -3     2         6

2                1                  0.9        1.3     14      18      21

2                2                  0.3        1.7      19      22     14

2                3                  -0.6        0.4     5        4       -7

the rows marked in red are what I want.

 

any idea?

 

Thanks all,

Chen

Super User
Posts: 19,861

Re: Difference between values in two rows

Output;

Lab1=dif(lab1); lab2=dif(lab2);.......etc;

If timePoint = 2 then do;
TimePoint=3;
Output;
End;

Explicitly output the records. 

Use DIF to calculate the difference. 

Super User
Super User
Posts: 7,988

Re: Difference between values in two rows

Post test data in the form of a datastep!!

 

As such, this is only theory:

data inter;
  merge have (where=(timepoint=1))
            have (where=(timepoint=2) rename=(lab1=lbx1 lab2=lbx2...));
  by subject_id;
  timepoint=3;
  lab1=lab1-lbx1;
  lab2=lab2-lbx2;
  ...;
run;

data want;
  set have inter;
run;

proc sort data=want;
  by id timepoint;
run;
PROC Star
Posts: 7,491

Re: Difference between values in two rows

Same solution as @Reeza but, to save keystrokes (and reduce the chance of making a typo), I'd include an array:

 

data want;
  input subject_id  timepoint lab1-lab5;
  array labs(*) lab1-lab5;
  Output;

  do i=1 to 5;
    Labs(i)=dif(labs(i));
  end;

  If timePoint = 2 then do;
    TimePoint=3;
    Output;
  end;
  cards;
1  1   0.5    0.6    15     18   12
1  2   0.4    0.6    12     20   18
2  1   0.9    1.3    14     18   21
2  2   0.3    1.7    19     22   14
;

Art, CEO, AnalystFinder.com

 

Ask a Question
Discussion stats
  • 3 replies
  • 486 views
  • 0 likes
  • 4 in conversation