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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: