Historical data

[AshokD]

Hi All,

I've a sample data below:- 

data test;
infile datalines;
input cus year month NTB ca_ind sav_ind loan_ind;
datalines;

1000 2022 08 1 0 0 1
1000 2022 09 0 0 0 1

1000 2022 10 0 0 1 1

2000 2022 08 0 0 0 0
2000 2022 09 0 0 0 0

2000 2022 10 1 0 1 0

;
run;

I have two cases now:-

#1. To find the customers who took the savings account through NTB in the oct'22 month.

#2.To find the customers who took the savings account but not through NTB in the oct'22 month.

proc sort data =test; by cus year month;

data NTBSAV OTHSAV;

set test;

by cus;

if first.cus and NTB = 1 AND sav_ind = 1 THEN OUTPUT NTBSAV ;

RUN;

#1. This logic is working fine. cus id = 2000 is reported in NTBSAV  dataset. 

#2.  Actually in OTHSAV dataset cus id =1000  should be reported. But I'm not sure about the logic Since I need to check historically for NTB field and other products indicator as well. 

Could someone of you please help me here ?

Thanks in advance.

[sbxkoenk]

Is this what you want??

( question is not fully clear to me )

data test;
infile datalines;
input cus year month NTB ca_ind sav_ind loan_ind;
datum=MDY(month,15,year);
format datum ddmmyy10.;
datalines;
1000 2022 08 1 0 0 1
1000 2022 09 0 0 0 1
1000 2022 10 0 0 1 1
2000 2022 08 0 0 0 0
2000 2022 09 0 0 0 0
2000 2022 10 1 0 1 0
;
run;

data took_sav_throughNTB_oct22;
 set test;
 where datum = '15OCT2022'd;
 if (NTB=1 AND sav_ind=1) then output;
run;

data took_sav_NOTthroughNTB_oct22;
 set test;
 where datum = '15OCT2022'd;
 if (NTB=0 AND sav_ind=1) then output;
run;
/* end of program */

Koen

[sbxkoenk]

Or rather like this (with one data step only!) :

data test;
infile datalines;
input cus year month NTB ca_ind sav_ind loan_ind;
datum=MDY(month,15,year);
format datum ddmmyy10.;
datalines;
1000 2022 08 1 0 0 1
1000 2022 09 0 0 0 1
1000 2022 10 0 0 1 1
2000 2022 08 0 0 0 0
2000 2022 09 0 0 0 0
2000 2022 10 1 0 1 0
;
run;

data took_sav_throughNTB_oct22
     took_sav_NOTthroughNTB_oct22;
 set test;
 where datum = '15OCT2022'd;
 if      (NTB=1 AND sav_ind=1) then output took_sav_throughNTB_oct22;
 else if (NTB=0 AND sav_ind=1) then output took_sav_NOTthroughNTB_oct22;
 else;
run;
/* end of program */

Koen

[AshokD]

Apologies, Let me make it more clear with the below data sample:-

datalines;
1000 2022 08 1 0 0 1
1000 2022 09 0 0 0 1
1000 2022 10 0 0 1 1

3000 2022 08 1 1 1 0
3000 2022 09 0 1 1 0
3000 2022 10 0 1 1 0
2000 2022 08 0 0 0 0
2000 2022 09 0 0 0 0
2000 2022 10 1 0 1 0
;

NTB(New to Business) indicator will be the main key for us, which we can identify -

NTB through Savings or

NTB through other products

For Customer id:1000 - NTB through loan product on Aug'22 and took savings on Oct'22 which is good to report in "took_sav_NOTthroughNTB_oct22" dataset. 

For Customer id:3000- NTB through savings and CA products on Aug'22 and they are still active till Oct'22 but they are not new to savings as of Oct'22, so this customer should not be reported in "took_sav_NOTthroughNTB_oct22" dataset.

So, I'm looking -  if someone took savings products in Oct'22 but they are not NTB. 

Hope it is clear now. 

Kindly share your thoughts.

[Patrick]

If I got you right then something like below could work. 

data test;
  infile datalines;
  input cus year month NTB ca_ind sav_ind loan_ind;
  datum=MDY(month,15,year);
  format datum ddmmyy10.;
  datalines;
1000 2022 08 1 0 0 1
1000 2022 09 0 0 0 1
1000 2022 10 0 0 0 1
3000 2022 08 1 1 1 0
3000 2022 09 0 1 1 0
3000 2022 10 0 1 1 0
2000 2022 08 0 0 0 0
2000 2022 09 0 0 0 0
2000 2022 10 1 0 1 0
;

proc sql;
  create view v_oct22 as
    select cus, sum(ntb,sav_ind) as ntb_sav_ind_oct22, sav_ind, ntb, datum
    from test 
    where sav_ind=1 and put(datum,yymmn6.) = '202210'
  ;
  select * from v_oct22
  ;
  select 
    l.*, 
    coalesce(ntb_sav_ind_oct22,0) as ntb_sav_ind_oct22
  from 
    test l 
    left join 
    v_oct22 as r
      on l.cus=r.cus
  ;
quit;

[AshokD]

Nope, this is not working. Please look at the below sample data which I've provided. It seems like, sample data was modified in your logic. Also, please refer below for more detail about my expectations.

Once again thanks for your time. 

[Kurt_Bremser]

So you want all those which have NTB any time in the dataset, and sav_ind in the last observation?

[AshokD]

Nope, In simple note - I just want the customers who took new savings account in Oct'22. But we have two cases-

data NTBSAV OTHSAV;

SET TEST;

??????

????

RUN;

NTBSAV dataset- Customer who is NTB(New to Business - first entry historically) and took savings account in Oct'22 [Cust ID: 2000  should be reported]

OTHSAV dataset- Customer took savings account in Oct'22 but not NTB, since he/she already NTB through loan product. [Cust id 1000 should be reported]

Note -

#1. We have to analyze historically other products and previous months entries as well.

#2. Cust Id:3000 should not be reported in both the datasets.

Sample data below:-

data test;

infile datalines;

input cus year month NTB ca_ind sav_ind loan_ind;

datalines;

1000 2022 08 1 0 0 1
1000 2022 09 0 0 0 1
1000 2022 10 0 0 1 1

3000 2022 08 1 1 1 0
3000 2022 09 0 1 1 0
3000 2022 10 0 1 1 0
2000 2022 08 0 0 0 0
2000 2022 09 0 0 0 0
2000 2022 10 1 0 1 0
;

run;

[Kurt_Bremser]

So, when a customer is NTB and has sav_ind in October, but no previous NTB, they go into ntbsav.

If there's a previous NTB without sav_ind, but sav_ind in October, they go into othsav.

Those without any NTB, or no sav_ind in October, are not reported at all.

Are there any observations after October 2022 in the dataset which need to be filtered out?

[AshokD]

Yeah, Exactly. 

Oct'22 month will be the final data and there is no data after that.

[Kurt_Bremser · Accepted solution]

proc sort data=have;
by cus year month;
run;

data
  ntbsav
  othsav
;
do until (last.cus);
  srt have;
  by cus;
  if ntb then _ntb = 1;
end;
if sav_ind
then do;
  if _ntb then output othsav;
  else if ntb then output ntbsav;
end;
drop _ntb;
run;

[AshokD]

It is working fine now, thanks for your help.