## Fill in the missing values with the previous values

Solved
Frequent Contributor
Posts: 92

# Fill in the missing values with the previous values

Hi all,

I tried to fill the blanks with the previous values, as show below:

this is the original data look like.

 ID VISIT value1 value2 value3 A 1 5 7 9 A 2 A 3 A 4 10 5 3 A 5 B 1 11 2 6 B 2 B 3 16 7 9 B 4 B 5 18 20 5

this is what I want  ---  fill the blank with the previous data.

so A2 is filling with A1 values as well as A3 and so on.

 ID VISIT value1 value2 value3 A 1 5 7 9 A 2 5 7 9 A 3 5 7 9 A 4 10 5 3 A 5 10 5 3 B 1 11 2 6 B 2 11 2 6 B 3 16 7 9 B 4 16 7 9 B 5 18 20 5

I wonder how to write the codes to get what I want.

Any help will be very appreciate.

Thank you,

Accepted Solutions
Solution
‎10-01-2015 06:36 PM
Super User
Posts: 6,200

## Re: Fill in the missing values with the previous values

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

All Replies
Solution
‎10-01-2015 06:36 PM
Super User
Posts: 6,200

## Re: Fill in the missing values with the previous values

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

Learner
Posts: 1

## Re: Fill in the missing values with the previous values

I realy like your solution. It works fine for me. Bur for learning purposes I try to understand why you need the obs=0 option. Can you explain?
Contributor
Posts: 70

## Re: Fill in the missing values with the previous values

Hi, I faced the same problem and tried to use array function. I think I messed up somewhere in the array function. I want to use array function in this problem. Could anyone please tell me what is the problem in my code.

DATA want;
SET have;
array value[*] value1-value3;
array _variable[3];
retain _variable[i];
if not missing(value[i]) then _variable [i] =value [i];
else value [i]=_variable [i];
drop _variable[i];
run;

Valued Guide
Posts: 863

## Re: Fill in the missing values with the previous values

Here you go.  If you real data is much different than what you provided there might be a much better route:

data have;
infile cards;
input ID\$     VISIT     value1     value2     value3;
cards;
A     1     5     7     9
A     2
A     3
A     4     10     5     3
A     5
B     1     11     2     6
B     2
B     3     16     7     9
B     4
B     5     18     20     5
;

data want;
set have;
retain _value1 _value2 _value3;
if not missing(value1) then _value1 = value1;
if not missing(value2) then _value2 = value2;
if not missing(value3) then _value3 = value3;
if missing(value1) then value1 = _value1;
if missing(value2) then value2 = _value2;
if missing(value3) then value3 = _value3;
drop _:;
run;

Frequent Contributor
Posts: 92

## Re: Fill in the missing values with the previous values

thanks so much for the codes.

It works!

How about if I have more variables to fill them in, upto 50 variables for example, I know I can use your codes by writing each of the variables names.

I wonder is there any other more simple codes to write upto 50 variables names to fill in?

Posts: 3,162

## Re: Fill in the missing values with the previous values

I bet you haven't tried the solution by @Astounding

Frequent Contributor
Posts: 92

## Re: Fill in the missing values with the previous values

Yes, it works!!

I really appreciate to all of you for the solution.

you have made the community such a wonderful place to ask question and get the solution.

Thanks every body!

Regular Contributor
Posts: 161

## Re: Fill in the missing values with the previous values

Another solution to your requirement would be the use of LAG () function which can help.  Well, you already have a few solutions and perhaps might have this in your pocket available too some day..

Good Luck to you...!!!

Kannan Deivasigamani
Contributor
Posts: 70

## Re: Fill in the missing values with the previous values

Hi, I faced the same problem and tried to use array function. I think I messed up somewhere in the array function. I want to use array function in this problem. Could anyone please tell me what is the problem in my code.

DATA want;
SET have;
array value[*] value1-value3;
array _variable[3];
retain _variable[i];
if not missing(value[i]) then _variable [i] =value [i];
else value [i]=_variable [i];
drop _variable[i];
run;

☑ This topic is solved.