DATA Step, Macro, Functions and more

Fill in the missing values with the previous values

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

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: 5,079

Re: Fill in the missing values with the previous values

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

View solution in original post


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

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: 63

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: 858

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;

Contributor
Posts: 64

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?

 

Thanks for your time!

Respected Advisor
Posts: 3,124

Re: Fill in the missing values with the previous values

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

Contributor
Posts: 64

Re: Fill in the missing values with the previous values

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!

 

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: 63

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 1286 views
  • 12 likes
  • 7 in conversation