topic Re: Restructure Repeated Measures Data in Tall Format and Count Time Between Non-Missing Values in SAS Programming

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

Tallefield — Thu, 02 Mar 2023 20:24:20 GMT

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!

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

ballardw — Thu, 02 Mar 2023 22:08:39 GMT

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.

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

Tallefield — Fri, 03 Mar 2023 14:05:34 GMT

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

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

FreelanceReinh — Fri, 03 Mar 2023 14:54:36 GMT

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.

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

Tallefield — Fri, 03 Mar 2023 17:52:26 GMT

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