BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Takdir
Obsidian | Level 7
DateMaturityRate
01-Mar15
01-Mar26
01-Mar3 
01-Mar48
01-Mar5 
02-Mar1 
02-Mar211
02-Mar312
02-Mar413
02-Mar514
03-Mar120
03-Mar221
03-Mar322
03-Mar4 
03-Mar5 

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:

DateMaturityRate
01-Mar15
01-Mar26
01-Mar37
01-Mar48
01-Mar59
02-Mar110
02-Mar211
02-Mar312
02-Mar413
02-Mar514
03-Mar120
03-Mar221
03-Mar322
03-Mar423
03-Mar524

 

Thanks in advance 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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 

View solution in original post

2 REPLIES 2
novinosrin
Tourmaline | Level 20

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; 
PeterClemmensen
Tourmaline | Level 20

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 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 859 views
  • 1 like
  • 3 in conversation