topic Joining based on a condition in SAS Programming

Joining based on a condition

jb9977 — Mon, 04 Jun 2018 16:31:25 GMT

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.

Final table;

Col A Col B

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

Re: Joining based on a condition

novinosrin — Mon, 04 Jun 2018 16:48:44 GMT

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;

Re: Joining based on a condition

novinosrin — Mon, 04 Jun 2018 17:23:50 GMT

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;

Re: Joining based on a condition

Ksharp — Tue, 05 Jun 2018 13:19:43 GMT

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;