Solved
Contributor
Posts: 20

# Joining based on a condition

I have two tables

Table A

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;
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 sum=5 and k then do; output;k=.;end;
end;
keep FiscalDate b;
run;``````

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

## Re: Joining based on a condition

``````data a;
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 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;
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.defineDone();
end;
set b;
rc = iter.first();
sum=0;
k=0;
do while (rc = 0);
if FiscalDate=b then k=1;
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;
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;
if n=5;
run;
data want;
set temp1;
by b;
if first.b;
run;``````
☑ This topic is solved.