Filling upp missing values with previous values (panel)

[MCB2]

Hi,

I need to fill upp missing values with previous. Usually I can fix it but not this time. 

Data have	
input ID sjp_dgr;
cards;	
1 77	
1 .	
1 .	
1 .	
1 54	
1 27	
1 66	
2 .	
2  972	
2 .	
2 .	
2 .	
3 28	
3 24	
3 31	
3 1420	
3 .	
3 .	
4  747	
4 .	
5 .	
5 .	
5 .	
5 .	
5 51	
;	
Run;

data want;
set have;
retain sjp_dgr;
by id;
if not missing (sjp_dgr) then sjp_dgr_=sjp_dgr;
run;
	

 The problem is that by filling upp missing values in this way the last value for ID 1 (66) becomes the first non missing value for ID 2, while I need  that ID 2 keeps its missing value in its first record. A similar problem appears for ID 5 who has missing values originally until the first value appears (51) . In this case ID 4 last value (747) goes over to ID 5 which is not correct. All rows in the original data must remain, even it values are missing in some rows. This is becuase the real data inclues other columns where the values for those are not missing.  

thank!

[ballardw]

I'm not sure I understand all of your problem but an ancient "kludge" to process data in reverse order is to add a sequence variable,sort the data so that your records are in a different order, use the same process you use for "filling down" then resort to the original order: (I think your example code had the wrong variable name on the Retain statement)

data helper;
   set have;
   order = _n_;
run;

Proc sort data=helper;
   by id descending order;
run;

data helper2;
   set helper;
   retain sjp_dgr_;
   by id;
   if not missing(sgj_dgr) then sjp_dgr_ = sjp_dgr;
run;

proc sort data=helper2 out=want (drop=order);
   by id order;
run;

---

@MCB2 wrote:

Hi,

I need to fill upp missing values with previous. Usually I can fix it but not this time. 

Data have	
input ID sjp_dgr;
cards;	
1 77	
1 .	
1 .	
1 .	
1 54	
1 27	
1 66	
2 .	
2  972	
2 .	
2 .	
2 .	
3 28	
3 24	
3 31	
3 1420	
3 .	
3 .	
4  747	
4 .	
5 .	
5 .	
5 .	
5 .	
5 51	
;	
Run;

data want;
set have;
retain sjp_dgr;
by id;
if not missing (sjp_dgr) then sjp_dgr_=sjp_dgr;
run;
	

 The problem is that by filling upp missing values in this way the last value for ID 1 (66) becomes the first non missing value for ID 2, while I need  that ID 2 keeps its missing value in its first record. A similar problem appears for ID 5 who has missing values originally until the first value appears (51) . In this case ID 4 last value (747) goes over to ID 5 which is not correct. All rows in the original data must remain, even it values are missing in some rows. This is becuase the real data inclues other columns where the values for those are not missing.  

thank!

---

[MCB2]

Hi,

Thanks for your suggestion. Unfortunately it doesn't work. What I need to get at the end is the data shown in thel last column Sjp_dgr_ bellow:

ID Sjp_dgr_
1 77
1 77
1 77
1 77
1 54
1 27
1 66
2 .
2 972
2 972
2 972
2 972
3 28
3 24
3 31
3 1420
3 1420
3 1420
4 747
4 747
5 .
5 .
5 .
5 .
5 51

[ballardw]

I do not see how that is "filling up" That looks like filling down.

The reason you did not get the desired result is the code in your original post has the WRONG variable on retain:

This is what you posted:

data want;
set have;
retain sjp_dgr;
by id;
if not missing (sjp_dgr) then sjp_dgr_=sjp_dgr;
run;

 Since the variable sjp_dgr is the variable in the Have set retain basically accomplishes nothing as it is reset to the new incoming value from the set have.

Then you set the variable sjp_dgr_ only when sjp_dgr is not missing. Which is basically the same as not bothering with the IF part.

You wanted to retain the variable SJP_DGR_ , which would keep that NEW variables values.

[Ksharp · Accepted solution]

Data have ;
input ID sjp_dgr;
cards; 
1 77 
1 . 
1 . 
1 . 
1 54 
1 27 
1 66 
2 . 
2  972 
2 . 
2 . 
2 . 
3 28 
3 24 
3 31 
3 1420 
3 . 
3 . 
4  747 
4 . 
5 . 
5 . 
5 . 
5 . 
5 51 
; 
Run;

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

[MCB2]

Thank you, this works perfectly. I solved it by using Coalesce function and some created help variables as first_id, last_id and lag(id). The result was the same as yours, but your solution is much better. 🙂