missing values - fill the new ones

monday89 — Mon, 16 Nov 2020 16:40:39 GMT

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

Re: missing values - fill the new ones

ballardw — Mon, 16 Nov 2020 16:48:45 GMT

@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).

topic missing values - fill the new ones in SAS Programming

missing values - fill the new ones

Re: missing values - fill the new ones