turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Fill in the missing values with the previous value...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2015 03:18 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ursula

10-01-2015 03:38 PM

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

All Replies

Solution

10-01-2015
06:36 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ursula

10-01-2015 03:38 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

11-12-2015 05:45 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

05-19-2017 04:02 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ursula

10-01-2015 03:39 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Steelers_In_DC

10-01-2015 04:11 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ursula

10-01-2015 04:20 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Haikuo

10-01-2015 06:27 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ursula

10-22-2015 09:48 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ursula

05-19-2017 04:02 PM

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;