I need to merge two datasets on ID but I want rate value to be same from the top date of table2 until date chnages in table1 so for exampe in table 1 effective date is 18may09 and in table two it is from 18may09 untill th expiry date so I need t get the rate until 18maychanges to 1st June in Table
I have got two dtastes as below
Table1
ID | rate | Effective date |
123 | 5.62 | 18-May-09 |
123 | 4.88 | 01-Jun-09 |
123 | 4.25 | 01-Sep-09 |
123 | 4.12 | 01-Dec-09 |
123 | 4.25 | 01-Mar-10 |
123 | 4.37 | 01-Mar-11 |
123 | 4.5 | 01-Sep-11 |
123 | 4.62 | 01-Dec-11 |
123 | 4.5 | 01-Jun-12 |
123 | 4.25 | 03-Sep-12 |
123 | 4.12 | 03-Dec-12
|
Table 2 | ||||
Formmated Date | ID | close date | open dare | |
18-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
19-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
20-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
21-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
22-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
23-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
24-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
25-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
26-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
27-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
28-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
29-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
30-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
31-May-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
01-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
02-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
03-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
04-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
05-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
06-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
07-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
08-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
09-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
10-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
11-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
12-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
13-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
14-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
15-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
16-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
17-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
18-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
19-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
20-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
21-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
22-Jun-09 | 30-Apr-10 | 123 | 30-Jan-15 | 18-May-09 |
and I want result like this
Result | ||
ID | Rate | Formatted date |
123 | 5.62 | 18-May-09 |
123 | 5.62 | 19-May-09 |
123 | 5.62 | 20-May-09 |
123 | 5.62 | 21-May-09 |
123 | 5.62 | 22-May-09 |
123 | 5.62 | 23-May-09 |
123 | 5.62 | 24-May-09 |
123 | 5.62 | 25-May-09 |
123 | 5.62 | 26-May-09 |
123 | 5.62 | 27-May-09 |
123 | 5.62 | 28-May-09 |
123 | 5.62 | 29-May-09 |
123 | 5.62 | 30-May-09 |
123 | 5.62 | 31-May-09 |
123 | 5.62 | 01-Jun-09 |
123 | 4.88 | 02-Jun-09 |
123 | 4.88 | 03-Jun-09 |
123 | 4.88 | 04-Jun-09 |
123 | 4.88 | 05-Jun-09 |
123 | 4.88 | 06-Jun-09 |
123 | 4.88 | 07-Jun-09 |
123 | 4.88 | 08-Jun-09 |
123 | 4.88 | 09-Jun-09 |
123 | 4.88 | 10-Jun-09 |
123 | 4.88 | 11-Jun-09 |
123 | 4.88 | 12-Jun-09 |
123 | 4.88 | 13-Jun-09 |
123 | 4.88 | 14-Jun-09 |
123 | 4.88 | 15-Jun-09 |
123 | 4.88 | 16-Jun-09 |
123 | 4.88 | 17-Jun-09 |
123 | 4.88 | 18-Jun-09 |
123 | 4.88 | 19-Jun-09 |
123 | 4.88 | 20-Jun-09 |
123 | 4.88 | 21-Jun-09 |
123 | 4.88 | 22-Jun-09 |
Please post data in usable form - data steps using datalines statement.
data table1;
format Effective_date date9.;
infile cards dlm= "," dsd;
input id rate Effective_date ddmmyy10.;
cards ;
123,5.62,18/05/2009
123,4.88,01/06/2009
123,4.25,01/09/2009
123,4.12,01/12/2009
123,4.25,01/03/2010
123,4.37,01/03/2011
123,4.5 ,01/09/2011
123,4.62,01/12/2011
123,4.5 ,01/06/2012
123,4.25,03/09/2012
123,4.12,03/12/2012
;run;
data table2;
format Formmated_Date ddmmyy10. id 3. Close_dt ddmmyy10. Open_dt ddmmyy10.;
infile cards dlm= "," dsd ;
input Formmated_Date ddmmyy10. ID 3. Close_dt ddmmyy10. Open_dt ddmmyy10.;
cards ;
18/05/2009,123,30/01/2015,18/05/2009
19/05/2009,123,30/01/2015,18/05/2009
20/05/2009,123,30/01/2015,18/05/2009
21/05/2009,123,30/01/2015,18/05/2009
22/05/2009,123,30/01/2015,18/05/2009
23/05/2009,123,30/01/2015,18/05/2009
24/05/2009,123,30/01/2015,18/05/2009
25/05/2009,123,30/01/2015,18/05/2009
26/05/2009,123,30/01/2015,18/05/2009
27/05/2009,123,30/01/2015,18/05/2009
28/05/2009,123,30/01/2015,18/05/2009
29/05/2009,123,30/01/2015,18/05/2009
30/05/2009,123,30/01/2015,18/05/2009
31/05/2009,123,30/01/2015,18/05/2009
01/06/2009,123,30/01/2015,18/05/2009
02/06/2009,123,30/01/2015,18/05/2009
03/06/2009,123,30/01/2015,18/05/2009
04/06/2009,123,30/01/2015,18/05/2009
05/06/2009,123,30/01/2015,18/05/2009
06/06/2009,123,30/01/2015,18/05/2009
07/06/2009,123,30/01/2015,18/05/2009
08/06/2009,123,30/01/2015,18/05/2009
09/06/2009,123,30/01/2015,18/05/2009
10/06/2009,123,30/01/2015,18/05/2009
11/06/2009,123,30/01/2015,18/05/2009
12/06/2009,123,30/01/2015,18/05/2009
13/06/2009,123,30/01/2015,18/05/2009
14/06/2009,123,30/01/2015,18/05/2009
15/06/2009,123,30/01/2015,18/05/2009
16/06/2009,123,30/01/2015,18/05/2009
17/06/2009,123,30/01/2015,18/05/2009
18/06/2009,123,30/01/2015,18/05/2009
19/06/2009,123,30/01/2015,18/05/2009
20/06/2009,123,30/01/2015,18/05/2009
21/06/2009,123,30/01/2015,18/05/2009
22/06/2009,123,30/01/2015,18/05/2009
;run;
HI Andreas ,
I urgently need help on this query.
Did you manage to run the code and create the results please .
Hi @MV04
In your results, shouldn't
123 | 5.62 | 01-Jun-09 |
be 4.88?
as table1 effectivedate look up is
123 | 4.88 | 01-Jun-09 |
So if the above understanding and assumption of mine is correct, the following should work
data table1;
format Effective_date date9.;
infile cards dlm= "," dsd;
input id rate Effective_date ddmmyy10.;
cards ;
123,5.62,18/05/2009
123,4.88,01/06/2009
123,4.25,01/09/2009
123,4.12,01/12/2009
123,4.25,01/03/2010
123,4.37,01/03/2011
123,4.5 ,01/09/2011
123,4.62,01/12/2011
123,4.5 ,01/06/2012
123,4.25,03/09/2012
123,4.12,03/12/2012
;
run;
data table2;
infile cards dsd ;
input Formmated_Date :ddmmyy10. ID Close_dt :ddmmyy10. Open_dt : ddmmyy10.;
format Formmated_Date ddmmyy10. id 3. Close_dt ddmmyy10. Open_dt ddmmyy10.;
cards ;
18/05/2009,123,30/01/2015,18/05/2009
19/05/2009,123,30/01/2015,18/05/2009
20/05/2009,123,30/01/2015,18/05/2009
21/05/2009,123,30/01/2015,18/05/2009
22/05/2009,123,30/01/2015,18/05/2009
23/05/2009,123,30/01/2015,18/05/2009
24/05/2009,123,30/01/2015,18/05/2009
25/05/2009,123,30/01/2015,18/05/2009
26/05/2009,123,30/01/2015,18/05/2009
27/05/2009,123,30/01/2015,18/05/2009
28/05/2009,123,30/01/2015,18/05/2009
29/05/2009,123,30/01/2015,18/05/2009
30/05/2009,123,30/01/2015,18/05/2009
31/05/2009,123,30/01/2015,18/05/2009
01/06/2009,123,30/01/2015,18/05/2009
02/06/2009,123,30/01/2015,18/05/2009
03/06/2009,123,30/01/2015,18/05/2009
04/06/2009,123,30/01/2015,18/05/2009
05/06/2009,123,30/01/2015,18/05/2009
06/06/2009,123,30/01/2015,18/05/2009
07/06/2009,123,30/01/2015,18/05/2009
08/06/2009,123,30/01/2015,18/05/2009
09/06/2009,123,30/01/2015,18/05/2009
10/06/2009,123,30/01/2015,18/05/2009
11/06/2009,123,30/01/2015,18/05/2009
12/06/2009,123,30/01/2015,18/05/2009
13/06/2009,123,30/01/2015,18/05/2009
14/06/2009,123,30/01/2015,18/05/2009
15/06/2009,123,30/01/2015,18/05/2009
16/06/2009,123,30/01/2015,18/05/2009
17/06/2009,123,30/01/2015,18/05/2009
18/06/2009,123,30/01/2015,18/05/2009
19/06/2009,123,30/01/2015,18/05/2009
20/06/2009,123,30/01/2015,18/05/2009
21/06/2009,123,30/01/2015,18/05/2009
22/06/2009,123,30/01/2015,18/05/2009
;
run;
data want ;
if _n_=1 then do;
if 0 then set table1;
declare hash H (dataset:'table1') ;
h.definekey ("id","Effective_date") ;
h.definedata ("rate") ;
h.definedone () ;
end;
set table2;
rc=h.find(key:id,key:Formmated_Date);
keep ID Rate Formmated_Date;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.