topic Re: merge on ID in SAS Programming

merge on ID

MV04 — Sun, 14 Apr 2019 13:51:04 GMT

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

Re: merge on ID

andreas_lds — Sun, 14 Apr 2019 15:34:22 GMT

Please post data in usable form - data steps using datalines statement.

Re: merge on ID

MV04 — Sun, 14 Apr 2019 16:04:14 GMT

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;

Re: merge on ID

MV04 — Sun, 14 Apr 2019 16:29:33 GMT

HI Andreas ,

I urgently need help on this query.

Did you manage to run the code and create the results please .

Re: merge on ID

novinosrin — Sun, 14 Apr 2019 17:15:47 GMT

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;