CODEWITHSUNDEEP
sqlsas
Bharat Guda
Bharat Guda

Reputation: 117

sas/sql logic needed

I have a data with SSN and Open date and have to calculate if a customer has opened 2 or more accounts within 120 days based on the open_date field. I know to use INTCK/INTNX functions but it requires 2 date fields, not sure how to apply the same logic on a single field for same customer.Please suggest.

SSN  account         Open_date
xyz  000123          12/01/2015
xyz  112344          11/22/2015
xyz  893944          04/05/2016
abc  992343          01/10/2016
abc  999999          03/05/2016
123  111123          07/16/2015
123  445324          10/12/2015

Upvotes: 0

Views: 62

Answers (3)

Reeza
Reeza

Reputation: 21294

Just a slightly different solution here - use the dif function which calculates the number of days between accounts being open. 

proc sort data=have;
by ssn open_date;
run;

data want;
set have;
by ssn;

days_between_open = dif(open_date);

if first.ssn then days_between_open = .;

*if 0 < days_between_open < 120 then output;
run;

Then you can filter the table above as required. I've left it commented out at this point because you haven't specified how you want your output table.

Upvotes: 0

Dmitry Shopin
Dmitry Shopin

Reputation: 1763

I'd do it using JOIN :

proc sql;
  create table want as
  select *
  from have
  where SSN in
    (select a.SSN
    from have a
    inner join have b
    on a.SSN=b.SSN
    where intck('day', a.Open_date, b.Open_Date)+1 < 120)
  ;
quit;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use exists or join:

proc sql;
     select distinct SSN
     from t
     where exists (select 1
                   from t t2
                   where t2.SSN = t.SSN and
                         t2.open_date between t.open_date and t.open_date + 120
                  );

Upvotes: 1

Related Questions