How do I calculate missing values from available values in a variable?

[Shiv_K]

Hi,

I have the following data:

Size	Value
1	.
2	.
3	8.00
4	7.40
5	7.20
6	.
7	.
8	.
9	6.80
10	.
11	.
12	5.90
13	.
14	.
15	.

I want to calculate the missing values from the available values. For instance, values for sizes 1 and 2 are missing. The nearest available values are for sizes 3 and 4, and the values are 8.00 and 7.40. The difference in these sizes is 1 and the difference in the value is 0.60. Therefore the value of size 2 is 8.60 (that is, value of preceding size 3 at 8.00 plus the difference of 0.60) and the value of size 1 is 9.20 (that is, value of size 2 at 8.60 plus difference of 0.60). Similarly the values of sizes 6, 7 and 8 are calculated from the available values of sizes 5 and 9 as follows. The difference in the sizes is 4 and the difference of available values is 0.40 (that is, the size 5 value is 7.20 and size 9 value is 6.80) and the difference per size is 0.10 (that is, 0.40 divided by 4). Therefore the values of 6, 7 and 8 are 7.10, 7.00 and 6.90 respectively. The result will be like this.

Size	Value
1	9.20
2	8.60
3	8.00
4	7.40
5	7.20
6	7.10
7	7.00
8	6.90
9	6.80
10	6.50
11	6.20
12	5.90
13	5.60
14	5.30
15	5.00

Will someone please let me know how to write a program for this in SAS 9.4?

Thanks.

[PeterClemmensen · Accepted solution]

Classic linear interpolation with Proc Expand and the extrapolate option.

data have;
input Size Value;
datalines;
1 .     
2 .     
3 8.00  
4 7.40  
5 7.20  
6 .     
7 .     
8 .     
9 6.80  
10 .    
11 .    
12 5.90 
13 .    
14 .    
15 .    
;

 proc expand data = have out = want method=join extrapolate;
    convert Value;
    id Size;
 run;

Result:

Size  Value
1     9.2
2     8.6
3     8
4     7.4
5     7.2
6     7.1
7     7
8     6.9
9     6.8
10    6.5
11    6.2
12    5.9
13    5.6
14    5.3
15    5

[Shiv_K]

Thank you for providing the solution promptly.

[PeterClemmensen]

Anytime 🙂