How do I "fill down" in a table?

[MBI]

Hi all,
I have what I think is a relatively easy quesiton but I can't figure it out. I'm trying to make the following data table:

1
.
.
2
.
.
.
3
.
.

look like this:

1
1
1
2
2
2
2
3
3
3

essentially all I'm trying to do is to 'fill down' the null variables with the one above it. Is there an easy way to do this?

Thanks!

MBI

[Olivier · Accepted solution]

Hi MBI.
I'm afraid you can only do that programmatically. Here is some sample code : let's say you have a dataset called MyData, and a variable (column) called X.

DATA myDataFilled (DROP = filledX) ;
SET myData ;
RETAIN filledX ; /* keeps the last non-missing value in memory */
IF NOT MISSING(X) THEN filledX = X ; /* fills the new variable with non-missing value */
X = filledX ;
RUN ;

Regards.
Olivier

[njvelandia]

Excellent

[Nieves]

Hello @Olivier

may I ask what if the non missing value does not appear as the first observation? How to fill in the table in the following case?

group	time	value
1	Jan	.
1	Feb	10
1	Mar	.
2	Jan	.
2	Feb	.
2	Mar	20
3	Jan	30
3	Feb	.
3	Mar	.
4	Jan	.
4	Feb	40
4	Mar	.
5	Jan	50
5	Feb	.
5	Mar	.

and I want the data to be filled in as follows:

group	time	value
1	Jan	10
1	Feb	10
1	Mar	10
2	Jan	20
2	Feb	20
2	Mar	20
3	Jan	30
3	Feb	30
3	Mar	30
4	Jan	40
4	Feb	40
4	Mar	40
5	Jan	50
5	Feb	50
5	Mar	50

Thank you!

[apaez062]

Hi Nieves,

I am having the same problem right now where the value that I want pasted to all other records within each group is always the last...not the first. Did you ever get a solution to your question?

If so, could you forward that solution to me? I desperately need it.

Thank you!

[TomKari]

Just sort your data in reverse order, so the value you want is at the top. Once you've rippled your value, resort into the original sequence.

Tom

[MBI]

Ah, perfect! Easier than I thought. Thank you!!

[jelanseward]

AMAZING