topic Re: Update information from previous row in SAS Programming

Update information from previous row

Joey2 — Sun, 28 Feb 2021 19:17:55 GMT

I have a dataset with many patients first year visit information. Then I want to get some information from the first year data into the second and third year. For example, for ID=1, then his age will be 51, 52 in the 2nd and 3rd year, while gender is still male.

data have;
input id age visit gender $;
datalines;
1 50 1 male
1 .  2  .
1 .  3  .
2 60 1 female
2 .  2  .
2 .  3  .
;
run;

what I want

id	age	visit	gender
1	50	1	male
1	51	2	male
1	52	3	male
2	60	1	female
2	61	2	female

Thank you.

Re: Update information from previous row

PeterClemmensen — Sun, 28 Feb 2021 19:31:44 GMT

Try this


data have;
input id age visit gender $;
datalines;
1 50 1 male
1 .  2  .
1 .  3  .
2 60 1 female
2 .  2  .
2 .  3  .
;
run;

data want(drop = a g);
   set have;
   by id;
   if first.id then do;
      g = gender;
      a = age;
   end;
   else a + 1;

   gender = g;
   age    = a;
   

   retain a g;
run;

Re: Update information from previous row

Joey2 — Sun, 28 Feb 2021 20:01:51 GMT

It works! Thank you.

Re: Update information from previous row

Tom — Sun, 28 Feb 2021 20:10:59 GMT

You can actually trick the UPDATE statement into doing this for you. The purpose of the UPDATE statement is to allow you to apply a transaction dataset to an existing dataset. When the variable is the transaction dataset is missing then the current value is not replaced.

You have to have a BY group and normally the BY variables should uniquely identify the observations in the source dataset. But you can have multiple transactions per BY group. Your data already has a BY variable, ID, but it is not unique. But if you just treat ALL of the observations as the transactions your can avoid that problem. So use the OBS=0 dataset option to start with an empty source dataset. To output more than one observation per BY group just add an explicit OUTPUT statement.

data want;
  update have(obs=0) have;
  by id;
  output;
run;

Re: Update information from previous row

mkeintz — Sun, 28 Feb 2021 20:44:31 GMT

For future reference, consider a strategic combination of DROP= and POINT=:

data have;
input id age visit gender $;
datalines;
1 50 1 male
1 .  2  .
1 .  3  .
2 60 1 female
2 .  2  .
2 .  3  .
run;
data want;
  set have (drop=age gender);
  by id;
  if first.id then set have point=_n_;
run;

The benefit of this approach is that it avoids the need for an explicit RETAIN statement.

Re: Update information from previous row

Tom — Sun, 28 Feb 2021 20:50:10 GMT

That trick will only work when the non-missing values only appear on the first observation in the BY group.

Re: Update information from previous row

Joey2 — Mon, 01 Mar 2021 00:32:38 GMT

Your code would not give the updated age for 2nd and 3rd year:

id visit age gender

1	1	50	male
1	2	50	male
1	3	50	male
2	1	60	female
2	2	60	female
2	3	60	female

We need one more step to correct it:

data want;
set want;
if visit=2 then age=age+1;
if visit=3 then age=age+2;
run;