topic Re: interpolation by group in SAS Data Management

interpolation by group

Takdir — Thu, 17 Oct 2019 17:56:40 GMT

Date	Maturity	Rate
01-Mar	1	5
01-Mar	2	6
01-Mar	3
01-Mar	4	8
01-Mar	5
02-Mar	1
02-Mar	2	11
02-Mar	3	12
02-Mar	4	13
02-Mar	5	14
03-Mar	1	20
03-Mar	2	21
03-Mar	3	22
03-Mar	4
03-Mar	5

I wish to interpret the missing values of "rate" based on the known values of "maturity" and "rate". As you can see, data is divided into different groups by date. What I want is interpolation by different groups (date).

Output should be:

Date	Maturity	Rate
01-Mar	1	5
01-Mar	2	6
01-Mar	3	7
01-Mar	4	8
01-Mar	5	9
02-Mar	1	10
02-Mar	2	11
02-Mar	3	12
02-Mar	4	13
02-Mar	5	14
03-Mar	1	20
03-Mar	2	21
03-Mar	3	22
03-Mar	4	23
03-Mar	5	24

Thanks in advance 🙂

Re: interpolation by group

novinosrin — Thu, 17 Oct 2019 18:04:37 GMT


data have;
input Date $	Maturity	Rate;
cards;
01-Mar	1	5
01-Mar	2	6
01-Mar	3	. 
01-Mar	4	8
01-Mar	5	. 
02-Mar	1	. 
02-Mar	2	11
02-Mar	3	12
02-Mar	4	13
02-Mar	5	14
03-Mar	1	20
03-Mar	2	21
03-Mar	3	22
03-Mar	4	. 
03-Mar	5	.
; 

data want;
set have;
retain r;
if not missing(rate) then r=rate;
else do;
 rate=r+1; 
 r=rate;
end;
drop r;
run;

Re: interpolation by group

PeterClemmensen — Thu, 17 Oct 2019 18:11:48 GMT

If you have a SAS/ETS license, this is by far the easiest

data have;
input Date $ Maturity Rate;
infile datalines missover;
datalines;
01-Mar 1 5
01-Mar 2 6
01-Mar 3  
01-Mar 4 8
01-Mar 5  
02-Mar 1  
02-Mar 2 11
02-Mar 3 12
02-Mar 4 13
02-Mar 5 14
03-Mar 1 20
03-Mar 2 21
03-Mar 3 22
03-Mar 4  
03-Mar 5  
;

proc expand data=have out=want extrapolate;
   convert Rate=Rate / method=join ;
   by Date;
   id Maturity;
run;

Result:

Date   Maturity Rate 
01-Mar 1        5 
01-Mar 2        6 
01-Mar 3        7 
01-Mar 4        8 
01-Mar 5        9 
02-Mar 1        10 
02-Mar 2        11 
02-Mar 3        12 
02-Mar 4        13 
02-Mar 5        14 
03-Mar 1        20 
03-Mar 2        21 
03-Mar 3        22 
03-Mar 4        23 
03-Mar 5        24