topic Re: Filling the missing value in SAS Data Management

Filling the missing value

Miracle — Wed, 20 Jan 2016 07:45:08 GMT

Dear everyone,

Can I please ask how can I produce the desired output data (B) from input data (A)?

Your help is much appreciated.

Data A			Data B
id	yr	val	id	yr	val	val2
1	1	11	1	1	11	11
1	2	.	1	2	.	12
1	3	.	1	3	.	13
1	4	44	1	4	44	44
2	1	.	2	1	.	21
2	2	22	2	2	22	22
3	1	.	3	1	.	32
3	2	33	3	2	33	33
3	3	.	3	3	.	34
3	4	44	3	4	44	44
4	1	11	4	1	11	11
4	2	22	4	2	22	22
4	3	33	4	3	33	33
4	4	44	4	4	44	44
5	1	55	5	1	55	55
5	2	.	5	2	.	56
5	3	.	5	3	.	57
5	4	.	5	4	.	58
6	1	.	6	1	.	63
6	2	.	6	2	.	64
6	3	.	6	3	.	65
6	4	66	6	4	66	66

Re: Filling the missing value

Kurt_Bremser — Wed, 20 Jan 2016 08:26:20 GMT

proc sort data=have;
by id yr;
run;

data int;
set have;
by id;
retain val2;
if first.id then val2 = .;
if val ne . then val2 = val;
else if val2 ne . then val2 = val2 + 1;
run;

proc sort data=int;
by id descending yr;
run;

data want;
set int;
by id;
retain val3;
if val2 = . then val2 = val3;
else val3 = val2;
val3 = val3 - 1;
drop val3;
run;

proc sort data=want;
by id yr;
run;

Re: Filling the missing value

Ksharp — Wed, 20 Jan 2016 08:48:24 GMT

data have;
input id yr val;
cards;
1 1 11
1 2 . 	
1 3 .
1 4 44
2 1 . 	
2 2 22
;
run;
data temp;
 set have;
 by id;
 retain found;
 if first.id then call missing(found);
 if not missing(val) and not found then do;output;found=1;end;
run;
data want;
 merge have temp(keep=id yr val rename=(yr=_yr val=_val));
 by id;
 if missing(val) then val=_val+yr-_yr;
 drop _yr _val;
run;

Re: Filling the missing value

Miracle — Thu, 21 Jan 2016 00:57:12 GMT

Thank you very much KurtBremser and Kisharp.