topic Re: Fill in the missing values with the previous values in SAS Programming

Fill in the missing values with the previous values

ursula — Thu, 01 Oct 2015 19:18:56 GMT

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,

Re: Fill in the missing values with the previous values

Astounding — Thu, 01 Oct 2015 19:38:11 GMT

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

Re: Fill in the missing values with the previous values

Steelers_In_DC — Thu, 01 Oct 2015 19:39:42 GMT

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;

Re: Fill in the missing values with the previous values

ursula — Thu, 01 Oct 2015 20:11:57 GMT

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!

Re: Fill in the missing values with the previous values

Haikuo — Thu, 01 Oct 2015 20:20:57 GMT

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

Re: Fill in the missing values with the previous values

ursula — Thu, 01 Oct 2015 22:27:50 GMT

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!

Re: Fill in the missing values with the previous values

kannand — Fri, 23 Oct 2015 01:48:32 GMT

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

Re: Fill in the missing values with the previous values

bvdnberg — Thu, 12 Nov 2015 10:45:12 GMT

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?

Re: Fill in the missing values with the previous values

nazmul — Fri, 19 May 2017 20:02:02 GMT

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;

Re: Fill in the missing values with the previous values

nazmul — Fri, 19 May 2017 20:02:52 GMT