BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Tallefield
Calcite | Level 5

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:

IDIVDV
1..
113
1..
124
135
1..
123
223
2..
245

 

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

IDIVDVLag
1...
1142
1...
1251
1332
2252

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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.

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Calcite | Level 5

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

FreelanceReinh
Jade | Level 19

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
Calcite | Level 5

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1213 views
  • 1 like
  • 3 in conversation