Lag and first functions to create a new variable

[MFraga]

Hello,

I need to code a new_variable that has a first value that I determine and the others values use the information found one line above for another variable (old_variable) for the same ID variable. My dataset is organized in a longitudinal way.

Here it is an example of the structure of my dataset:

data have;

input id old_varaible;

datalines;

1 1

1 1

1 2

1 3

1 3

1 99

2 99

2 99

2 99

3 3

3 3

3 2

3 3

3 99

4 1

4 1

4 1

4 2

4 2

4 2

4 2

4 2

4 2

;

run;

I have tried to code like this, but it does not work:

data want;

set have;

new_variable = .;

if first.id and first.old_variable then new_variable = 9999;

if id = lag(id) then new_variable = lag(old_variable);

run;

What I want as a dataset would be:

data want;

set have;

input id old_varaible new_variable;

datalines;

1 1 9999

1 1 1

1 2 1

1 3 2

1 3 3

1 99 3

2 99 9999

2 99 99

2 99 99

3 3 9999

3 3 3

3 2 3

3 3 2

3 99 3

4 1 9999

4 1 1

4 1 1

4 2 1

4 2 2

4 2 2

4 2 2

4 2 2

4 2 2

;

run;

Does anyone have any clue ?

Thank you!

[ballardw]

Over 90 percent of the time attempts to use LAG variables after IF do not work. Read up on the separate queue nature of the lagged variable values.

Since your data is not sorted by ID and Old_variable your shown result does not match a requirement to use first.old_variable

(the ID=3 Old_variable=2)

Try RETAIN to keep a current value available for the next.

data want;
   set have;
   by id ;
   retain rvar;
   if first.id  then new_variable = 9999;
   Else  new_variable=rvar;
   rvar=old_variable;
   drop rvar;
run;

[kiranv_]

something like this.

data want;
set have;
by id;
new_variable = lag(old_varaible);
if first.id then new_variable =9999;
run;

[novinosrin · Accepted solution]

data have;

input id old_varaible;

datalines;
1 1
1 1
1 2
1 3
1 3
1 99
2 99
2 99
2 99
3 3
3 3
3 2
3 3
3 99
4 1
4 1
4 1
4 2
4 2
4 2
4 2
4 2
4 2
;

run;

data want;
set have;
by id;
new=ifn(first.id,9999,lag(old_varaible));
run;

[kiranv_]

very sleek answer

[novinosrin]

Thanks:)