Joining based on a condition

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Joining based on a condition

I have two tables 

 Table A 

 

FiscalDate                     BusinessDay
01JUN2018                              1
02JUN2018                              0
03JUN2018                              0
04JUN2018                             1
05JUN2018                              1
06JUN2018                              1
07JUN2018                              1
08JUN2018                              1
09JUN2018                              0
10JUN2018                             0

11JUN2018                             1

12JUN2018                             1

13JUN2018                             1

14JUN2018                             1

15JUN2018                             1

16JUN2018                             0

 

 

Table B

02JUN2018                             
03JUN2018                             
04JUN2018                            
05JUN2018                             
06JUN2018

 

I want an output where the sum of business days is 5.

 

Like

Final table;

Col A                   Col B

02JUN2018      08JUN2018                
03JUN2018      08JUN2018                
04JUN2018     08JUN2018                
05JUN2018      11JUN2018                
06JUN2018       12JUN2018


Accepted Solutions
Solution
4 weeks ago
PROC Star
Posts: 1,817

Re: Joining based on a condition

data a;
input FiscalDate       :date9.              BusinessDay;
format FiscalDate       date9. ;
cards;
01JUN2018                              1
02JUN2018                              0
03JUN2018                              0
04JUN2018                             1
05JUN2018                              1
06JUN2018                              1
07JUN2018                              1
08JUN2018                              1
09JUN2018                              0
10JUN2018                             0
11JUN2018                             1
12JUN2018                             1
13JUN2018                             1
14JUN2018                             1
15JUN2018                             1
16JUN2018                             0
;
data b;
input b:date9.;
format b date9.;
cards;
02JUN2018                             
03JUN2018                             
04JUN2018                            
05JUN2018                             
06JUN2018
;

data want;
set b;
sum=0;
k=0;
do n=1 to nobs;
set a nobs=nobs point=n;
if FiscalDate=b then k=1;
if k then sum+BusinessDay;
if sum=5 and k then do; output;k=.;end;
end;
keep FiscalDate b;
run;

View solution in original post


All Replies
Solution
4 weeks ago
PROC Star
Posts: 1,817

Re: Joining based on a condition

data a;
input FiscalDate       :date9.              BusinessDay;
format FiscalDate       date9. ;
cards;
01JUN2018                              1
02JUN2018                              0
03JUN2018                              0
04JUN2018                             1
05JUN2018                              1
06JUN2018                              1
07JUN2018                              1
08JUN2018                              1
09JUN2018                              0
10JUN2018                             0
11JUN2018                             1
12JUN2018                             1
13JUN2018                             1
14JUN2018                             1
15JUN2018                             1
16JUN2018                             0
;
data b;
input b:date9.;
format b date9.;
cards;
02JUN2018                             
03JUN2018                             
04JUN2018                            
05JUN2018                             
06JUN2018
;

data want;
set b;
sum=0;
k=0;
do n=1 to nobs;
set a nobs=nobs point=n;
if FiscalDate=b then k=1;
if k then sum+BusinessDay;
if sum=5 and k then do; output;k=.;end;
end;
keep FiscalDate b;
run;
PROC Star
Posts: 1,817

Re: Joining based on a condition

data a;
input FiscalDate       :date9.              BusinessDay;
format FiscalDate       date9. ;
cards;
01JUN2018                              1
02JUN2018                              0
03JUN2018                              0
04JUN2018                             1
05JUN2018                              1
06JUN2018                              1
07JUN2018                              1
08JUN2018                              1
09JUN2018                              0
10JUN2018                             0
11JUN2018                             1
12JUN2018                             1
13JUN2018                             1
14JUN2018                             1
15JUN2018                             1
16JUN2018                             0
;
data b;
input b:date9.;
format b date9.;
cards;
02JUN2018                             
03JUN2018                             
04JUN2018                            
05JUN2018                             
06JUN2018
;

data want;
if _N_ = 1 then do;
if 0 then set a;
      declare hash h(dataset: 'a', ordered: 'a');
      declare hiter iter('h');
      h.defineKey('FiscalDate');
      h.defineData('FiscalDate','BusinessDay');
      h.defineDone();
end;
set b;
rc = iter.first();
sum=0;
k=0;
do while (rc = 0);
if FiscalDate=b then k=1;
if k then sum+BusinessDay;
if sum=5 and k then do; output;k=.;end;
 rc = iter.next();
 end;
keep FiscalDate b;
run;
Super User
Posts: 10,784

Re: Joining based on a condition

data a;
input FiscalDate       :date9.              BusinessDay;
format FiscalDate       date9. ;
cards;
01JUN2018                              1
02JUN2018                              0
03JUN2018                              0
04JUN2018                             1
05JUN2018                              1
06JUN2018                              1
07JUN2018                              1
08JUN2018                              1
09JUN2018                              0
10JUN2018                             0
11JUN2018                             1
12JUN2018                             1
13JUN2018                             1
14JUN2018                             1
15JUN2018                             1
16JUN2018                             0
;
data b;
input b:date9.;
format b date9.;
cards;
02JUN2018                             
03JUN2018                             
04JUN2018                            
05JUN2018                             
06JUN2018
;
proc sql;
create table temp as
 select b.b,a.*
  from a,b
   where b.b <= a.FiscalDate
    order by b.b,a.FiscalDate;
quit;
data temp1;
 set temp;
 by b;
 if first.b then n=0;
 n+BusinessDay;
 if n=5;
run;
data want;
 set temp1;
 by b;
 if first.b;
run;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 117 views
  • 0 likes
  • 3 in conversation