missing values - fill the new ones

[monday89]

I have the following dataset:

What I would like to do is that:

1) If the first few observation of the group [i.e. rate 2 only] is blank, I would like to keep it blank

2) If the rate2 is filled for prior but not filled later, I would like to use rate2 for the same group until filled. 

3) The last observation for the group should be carried over prior value 

Name	rate1	rate2
AAA	0
AAA	25	22
AAA	50
AAA	75	45
AAA	100
BBB	0
BBB	25	10
BBB	50
BBB	75
BBB	100	71
CCC	0	10
CCC	25	21
CCC	50	51
CCC	75	65
CCC	100

WANT:

Name	rate1	rate2	want_rate2
AAA	0
AAA	25	22	22
AAA	50		22
AAA	75	45	45
AAA	100		45
BBB	0
BBB	25	10	10
BBB	50		10
BBB	75		10
BBB	100	71	71
CCC	0	10	10
CCC	25	21	21
CCC	50	51	51
CCC	75	65	65
CCC	100		100

To be honest, I have no idea how to starto.

I just have: 

if first.obs = null then want_rate2 = is null

[ballardw]

---

@monday89 wrote:

I have the following dataset:

What I would like to do is that:

1) If the first few observation of the group [i.e. rate 2 only] is blank, I would like to keep it blank

2) If the rate2 is filled for prior but not filled later, I would like to use rate2 for the same group until filled. 

3) The last observation for the group should be carried over prior value 

Name	rate1	rate2
AAA	0
AAA	25	22
AAA	50
AAA	75	45
AAA	100
BBB	0
BBB	25	10
BBB	50
BBB	75
BBB	100	71
CCC	0	10
CCC	25	21
CCC	50	51
CCC	75	65
CCC	100

WANT:

Name	rate1	rate2	want_rate2
AAA	0
AAA	25	22	22
AAA	50		22
AAA	75	45	45
AAA	100		45
BBB	0
BBB	25	10	10
BBB	50		10
BBB	75		10
BBB	100	71	71
CCC	0	10	10
CCC	25	21	21
CCC	50	51	51
CCC	75	65	65
CCC	100		100

To be honest, I have no idea how to starto.

I just have: 

if first.obs = null then want_rate2 = is null

---

Please post data in the form of a data step that will run and in a code box opened with either the </> or "running man" icon on the forum.

data have;
input Name $ 	rate1 	rate2 ;
datalines;
AAA 	0 	   .
AAA 	25 	22
AAA 	50 	. 
AAA 	75 	45
AAA 	100 	. 
BBB 	0 	   .
BBB 	25 	10
BBB 	50 	. 
BBB 	75 	. 
BBB 	100 	71
CCC 	0 	   10
CCC 	25 	21
CCC 	50 	51
CCC 	75 	65
CCC 	100 	.
;

data want;
   set have;
   by name;
   retain want_rate2;
   if first.name then call missing(want_rate2);
   if not missing(rate2) then want_rate2=rate2;
run;
 

 

If your actual data is not sorted by Name but is grouped then use BY NOTSORTED NAME;

The Retain statement is used to keep the value of a variable that is not in the contributing set across iterations of the data step boundary.

SAS typically doesn't use "is null" but the function call missing will assign missing values to all variables used or you can use the variable = . (for numeric values) or variable = '' (for character values).