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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.