%macro printme(in1); proc print data=&in1(obs=50); title "%upcase(&in1)"; run; proc contents data=&in1; title "%upcase(&in1)"; run; %mend printme; data have; infile datalines truncover; input customer_no $ acctno $ open_date; datalines; A 999 20221201 B 999 20221201 B 888 20221201 C 888 20221201 A 777 20221201 C 777 20221201 D 666 20221201 E 666 20221201 F 555 20221205 G 555 20221205 G 444 20221206 H 444 20221206 F 333 20221207 H 333 20221207 I 222 20221209 J 222 20221209 K 222 20221209 ; proc sql; create table tble1_1 as select distinct h1.open_date , h1.acctno , h1.customer_no , h2.open_date as h2_open_date from have h1 left join have h2 on h1.customer_no = h2.customer_no order by h1.open_date, h2.open_date desc, h1.acctno, h1.customer_no ; quit; %printme(tble1_1); proc sql; create table tble1_base as select open_date , acctno , customer_no , min(h2_open_date) As cust_acct_min_open_dt from tble1_1 group by open_date , acctno , customer_no order by open_date, acctno, customer_no desc ; quit; %printme(tble1_base); proc sql; create table tble1_acctno as select acctno , min(h2_open_date) As acct_min_open_dt from tble1_1 group by acctno ; quit; %printme(tble1_acctno); proc sql; create table tble1_customer as select customer_no , min(open_date) As cust_min_open_dt from tble1_1 group by customer_no ; quit; %printme(tble1_customer); proc sql; create table tble1_2 As select base.open_date , base.acctno , base.customer_no /* , base.cust_acct_min_open_dt*/ , acct.acct_min_open_dt , cust.cust_min_open_dt from tble1_base base inner join tble1_acctno acct on base.acctno = acct.acctno inner join tble1_customer cust on base.customer_no = cust.customer_no ; quit; %printme(tble1_2); data _null_; if _n_ = 1 then do; if 0 then set tble1_2; /* Add variables from the view */ /* to the PDV for WORK.NEW */ declare hash ht(ordered: 'a'); ht.definekey ('open_date','acctno'); ht.definedata('open_date','acctno','customer_no','cust_min_open_dt','acct_min_open_dt','ind_candidate'); ht.definedone(); declare hash custno(); custno.definekey('customer_no'); custno.definedone(); end; do while(not last); set tble1_2 end=last; open_date = open_date; acctno = acctno; /* customer_no = customer_no;*/ if (open_date = cust_min_open_dt) and (open_date = acct_min_open_dt) then do; if ht.find() NE 0 and custno.find() NE 0 then do; ind_candidate=1; rc1=ht.add(); rc2=custno.add(); end; end; if last then do; ht.output(dataset:'want'); /* write data using HashSort */ custno.output(dataset:'clients'); end; end; run;
... View more