CODEWITHSUNDEEP
saslag
Ellie
Ellie

Reputation: 75

How to find the previous value of a variable in SAS

I am trying to populate a variable with set conditions.

If the variable NUMBER is equal to . then make it equal to the previous value of Number.

data table2;
  set table1;

  prv_value = lag(number);
  if number = . then number = prv_value;
run;

However, if there are two values in a row where number = . then the second occurrence of the value of . has it's previous value of number = . .

What I would like is to find the first previous occurrence where number is not equal to . and make the value of number equal to that previous non-missing value.

An example of my data is:

id  number
d10   2       
d10   2       
d10   3       
d10   .       
d10   .

Applying the above code I would then get:

id  number prv_number
d10   2       .
d10   2       2
d10   3       2
d10   3       3
d10   .       .

What I would like is:

id  number prv_number
d10   2       .
d10   2       2
d10   3       2
d10   3       3
d10   3       .

i.e. if number has a value of . then make it equal to the first previous occurrence of a non-missing value.

In some cases in my dataset I might have:

id  number
d10   4
d10   2
d10   .
d10   .
d10   .
d10   .

and what I would like is:

id  number
d10   4
d10   2
d10   2
d10   2
d10   2
d10   2

(Note, my data is sorted in order of events/time, hence wanting the first previous occurence).

Is there anything I can do with the lag function over different number of previous occurrences, or anything else that can do what I'd like?

Upvotes: 4

Views: 7693

Answers (1)

Amir
Amir

Reputation: 1000

You could try using the retain statement as follows (untested):

data table2;
  set table1;

  retain prv_value .;

  if number=. then
    number=prv_value;

  if number ne . then
    prv_value=number;
run;

Upvotes: 6

Related Questions