Fill in the missing values with the previous values

[ursula]

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,

[Astounding · Accepted solution]

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

[bvdnberg]

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?

[nazmul]

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;

[Steelers_In_DC]

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;

[ursula]

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?

Thanks for your time!

[Haikuo]

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

[ursula]

Yes, it works!!

It's very helpful!!

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!

[kannand]

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...!!!

[nazmul]

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;