Restructure Repeated Measures Data in Tall Format and Count Time Between Non-Missing Values

[Tallefield]

Hi,

I have repeated assessments that the number of non-missing values varies across participants. I need to a) restructure the data such that an IV aligns with a DV at the next observation that is not missing and b) create a new variable that counts the lag between observations. 

For example, if I start with data like this:

ID	IV	DV
1	.	.
1	1	3
1	.	.
1	2	4
1	3	5
1	.	.
1	2	3
2	2	3
2	.	.
2	4	5

I would like to end up with data like the following: 

ID	IV	DV	Lag
1	.	.	.
1	1	4	2
1	.	.	.
1	2	5	1
1	3	3	2
2	2	5	2

Thank you!

[ballardw]

Please be careful when using terms that are also SAS functions. You may not be aware that LAG is a SAS function that returns the value of variable from previous observations. I really don't follow what you are requesting but it appears that your "Lag" is looking for something related to following observations.

[Tallefield]

Thanks for the advice. The code would need to count the timespan between the assessments - this is what I mean by lag. 

[FreelanceReinh · Accepted solution]

Hi @Tallefield,

This works for your sample data:

data want(drop=_:);
do _i=1 by 1 until(last.id);
  set have;
  by id;
  if dv>. then do;
    _k=sum(_k,1);
    _t=lag(_i);
  end;
end;
if _k=1 then _t=.;
do _i=1 to _i;
  set have curobs=_c;
  if _i<=_t then do;
    if iv=. then output;
    else do;
      do Lag=1 by 1 until(dv>.);
        _p=_c+Lag;
        set have(keep=dv) point=_p;
      end;
      output;
      Lag=.;
    end;
  end;
end;
run;

Maybe you can use it as a starting point for your real data.

[Tallefield]

Thank you so much! Also works great in the real data. Thanks again!