☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Calcite | Level 5

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

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

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```
3 REPLIES 3
Tourmaline | Level 20

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

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```
Calcite | Level 5

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

Thank you for providing the solution promptly.

Tourmaline | Level 20

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

Anytime 🙂

Discussion stats
• 3 replies
• 516 views
• 2 likes
• 2 in conversation