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
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.