join two tables with two dates

[K1235]

Hello,

I have two dataset and in each there are two diff dates, one which reports end of the month and other end of the business week

data a: ( reports end of the month date)

ID	Date	Val
1	1/31/2012	-0.47
1	2/29/2012	0.73
1	3/31/2012	-0.948
1	4/30/2012	-0.74
1	5/31/2012	6
1	6/30/2012	-4
1	7/31/2012	2
1	8/31/2012	-2
1	9/30/2012	-0.7
1	10/31/2012	-1.063
1	11/30/2012	1.46
1	12/31/2012	3.3

data b: ( reports end of the month during closing of the business week day.. no weekends)             

ID	Date	closing
1	1/31/2012	9.52
1	2/29/2012	9.59
1	3/30/2012	9.5
1	4/30/2012	9.43
1	5/31/2012	10
1	6/29/2012	9.56
1	7/31/2012	9.77
1	8/31/2012	9.57
1	9/28/2012	9.5
1	10/31/2012	9.4
1	11/30/2012	9.54
1	12/31/2012	9.78

with the current code, when I join the two dataset I get:

Data sample;
    Merge a (in=x) b (in=z);
    by id date;
run;

ID	Date	Val
1	1/31/2012	9.52
1	2/29/2012	9.59
1	3/30/2012	9.5
1	3/31/2012	.
1	4/30/2012	9.43
1	5/31/2012	10
1	6/29/2012	9.56
1	6/30/2012	.
1	7/31/2012	9.77
1	8/31/2012	9.57
1	9/28/2012	9.5
1	9/30/2012	.
1	10/31/2012	9.4
1	11/30/2012	9.54
1	12/31/2012	9.78

But what I want is:

ID	Date	Val	closing
1	2012/01	-0.47	9.52
1	2012/02	0.73	9.59
1	2012/03	-0.948	9.5
1	2012/04	-0.74	9.43
1	2012/05	6	10
1	2012/06	-4	9.56
1	2012/07	2	9.77
1	2012/08	-2	9.57
1	2012/09	-0.7	9.5
1	2012/10	-1.063	9.4
1	2012/11	1.46	9.54
1	2012/12	3.3	9.78

[ballardw · Accepted solution]

It appears that your rule is to match the two sets where Month and Year align.

I may be better to use an SQL join than a data step merge since the values aren't always equal.

data a;
input id date mmddyy10. val;
format date mmddyy10.;
datalines;
1 1/31/2012 -0.47 
1 2/29/2012 0.73 
1 3/31/2012 -0.948 
1 4/30/2012 -0.74 
1 5/31/2012 6 
1 6/30/2012 -4 
1 7/31/2012 2 
1 8/31/2012 -2 
1 9/30/2012 -0.7 
1 10/31/2012 -1.063 
1 11/30/2012 1.46 
1 12/31/2012 3.3 
;
run;
 

data b;
input id date mmddyy10. closing;
format date mmddyy10.;
datalines;
1 1/31/2012 9.52 
1 2/29/2012 9.59 
1 3/30/2012 9.5 
1 4/30/2012 9.43 
1 5/31/2012 10 
1 6/29/2012 9.56 
1 7/31/2012 9.77 
1 8/31/2012 9.57 
1 9/28/2012 9.5 
1 10/31/2012 9.4 
1 11/30/2012 9.54 
1 12/31/2012 9.78 
;
run;

proc sql; 
   create table want as
   select a.id,a.date ,a.val, b.closing
   from a left join b 
      on
       a.id = b.id and
       intnx('month',a.date,0,'B') = intnx('month',b.date,0,'B')
   ;
quit;

[K1235]

That worked!!! Can you please explain me this 

intnx('month',a.date,0,'B') = intnx('month',b.date,0,'B')

Thanks!

[Ksharp]

You need GROUPFORMAT option.

data a;
input id date mmddyy10. val;
format date mmddyy10.;
datalines;
1 1/31/2012 -0.47 
1 2/29/2012 0.73 
1 3/31/2012 -0.948 
1 4/30/2012 -0.74 
1 5/31/2012 6 
1 6/30/2012 -4 
1 7/31/2012 2 
1 8/31/2012 -2 
1 9/30/2012 -0.7 
1 10/31/2012 -1.063 
1 11/30/2012 1.46 
1 12/31/2012 3.3 
;
run;
 

data b;
input id date mmddyy10. closing;
format date mmddyy10.;
datalines;
1 1/31/2012 9.52 
1 2/29/2012 9.59 
1 3/30/2012 9.5 
1 4/30/2012 9.43 
1 5/31/2012 10 
1 6/29/2012 9.56 
1 7/31/2012 9.77 
1 8/31/2012 9.57 
1 9/28/2012 9.5 
1 10/31/2012 9.4 
1 11/30/2012 9.54 
1 12/31/2012 9.78 
;
run;
Data sample;
    Merge a (in=x) b (in=z);
    by id date groupformat;
	format date monyy7.;
run;

[K1235]

Thank you @Ksharp and @ballardw. Need to add one more filter to this.. I am getting historical data and need only 5 years of data. So from This month - 5 years. How would I use INTNX function?

[Ksharp]

%let start=%sysfunc(intnx(month,%sysfunc(today()),-60));
%put &start;

...........
if date > &start ;