## Filling the missing value

Solved
Regular Contributor
Posts: 249

# Filling the missing value

Dear everyone,

Can I please ask how can I produce the desired output data (B) from input data (A)?

 Data A Data B id yr val id yr val val2 1 1 11 1 1 11 11 1 2 . 1 2 . 12 1 3 . 1 3 . 13 1 4 44 1 4 44 44 2 1 . 2 1 . 21 2 2 22 2 2 22 22 3 1 . 3 1 . 32 3 2 33 3 2 33 33 3 3 . 3 3 . 34 3 4 44 3 4 44 44 4 1 11 4 1 11 11 4 2 22 4 2 22 22 4 3 33 4 3 33 33 4 4 44 4 4 44 44 5 1 55 5 1 55 55 5 2 . 5 2 . 56 5 3 . 5 3 . 57 5 4 . 5 4 . 58 6 1 . 6 1 . 63 6 2 . 6 2 . 64 6 3 . 6 3 . 65 6 4 66 6 4 66 66

Accepted Solutions
Solution
‎01-21-2016 09:43 PM
Super User
Posts: 9,923

## Re: Filling the missing value

``````proc sort data=have;
by id yr;
run;

data int;
set have;
by id;
retain val2;
if first.id then val2 = .;
if val ne . then val2 = val;
else if val2 ne . then val2 = val2 + 1;
run;

proc sort data=int;
by id descending yr;
run;

data want;
set int;
by id;
retain val3;
if val2 = . then val2 = val3;
else val3 = val2;
val3 = val3 - 1;
drop val3;
run;

proc sort data=want;
by id yr;
run;
``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎01-21-2016 09:43 PM
Super User
Posts: 9,923

## Re: Filling the missing value

``````proc sort data=have;
by id yr;
run;

data int;
set have;
by id;
retain val2;
if first.id then val2 = .;
if val ne . then val2 = val;
else if val2 ne . then val2 = val2 + 1;
run;

proc sort data=int;
by id descending yr;
run;

data want;
set int;
by id;
retain val3;
if val2 = . then val2 = val3;
else val3 = val2;
val3 = val3 - 1;
drop val3;
run;

proc sort data=want;
by id yr;
run;
``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,691

## Re: Filling the missing value

``````data have;
input id yr val;
cards;
1 1 11
1 2 .
1 3 .
1 4 44
2 1 .
2 2 22
;
run;
data temp;
set have;
by id;
retain found;
if first.id then call missing(found);
run;
data want;
merge have temp(keep=id yr val rename=(yr=_yr val=_val));
by id;
if missing(val) then val=_val+yr-_yr;
drop _yr _val;
run;``````
Regular Contributor
Posts: 249

## Re: Filling the missing value

Thank you very much KurtBremser and Kisharp.

🔒 This topic is solved and locked.