DATA Step, Macro, Functions and more

join two tables with two dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

join two tables with two dates

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)

IDDateVal
11/31/2012-0.47
12/29/20120.73
13/31/2012-0.948
14/30/2012-0.74
15/31/20126
16/30/2012-4
17/31/20122
18/31/2012-2
19/30/2012-0.7
110/31/2012-1.063
111/30/20121.46
112/31/20123.3

 

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

IDDateclosing
11/31/20129.52
12/29/20129.59
13/30/20129.5
14/30/20129.43
15/31/201210
16/29/20129.56
17/31/20129.77
18/31/20129.57
19/28/20129.5
110/31/20129.4
111/30/20129.54
112/31/20129.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;
IDDateVal
11/31/20129.52
12/29/20129.59
13/30/20129.5
13/31/2012.
14/30/20129.43
15/31/201210
16/29/20129.56
16/30/2012.
17/31/20129.77
18/31/20129.57
19/28/20129.5
19/30/2012.
110/31/20129.4
111/30/20129.54
112/31/20129.78


But what I want is:

IDDateValclosing
12012/01-0.479.52
12012/020.739.59
12012/03-0.9489.5
12012/04-0.749.43
12012/05610
12012/06-49.56
12012/0729.77
12012/08-29.57
12012/09-0.79.5
12012/10-1.0639.4
12012/111.469.54
12012/123.39.78

 




 


Accepted Solutions
Solution
‎09-28-2017 04:09 PM
Super User
Posts: 13,300

Re: join two tables with two dates

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;

View solution in original post


All Replies
Solution
‎09-28-2017 04:09 PM
Super User
Posts: 13,300

Re: join two tables with two dates

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;
Occasional Contributor
Posts: 7

Re: join two tables with two dates

That worked!!! Can you please explain me this 

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

Thanks!

 

 

Super User
Posts: 10,686

Re: join two tables with two dates

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;
Occasional Contributor
Posts: 7

Re: join two tables with two dates

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?

Super User
Posts: 10,686

Re: join two tables with two dates

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

...........
if date > &start ;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 222 views
  • 2 likes
  • 3 in conversation