Logic to find New Ins(New accounts) and Outs(Closed accounts) on Historic data

[BaalaRaaji]

Hi Guys,

I have got the historic data since Jan 2007 to June2019.

Any ideas or suggestions to create a automatic code by creating new flags for New Ins and Outs.

I have hard coded and got numbers  using the below code for 6 months but need to automate it.

Any help is much appreciated.

data CM;
set POS_CM;
where Reals_Status='Total_Possession' and END_OF_MONTH = "30JUN2019"d;
run;

data LM;
set POS_LM;
where Reals_Status='Total_Possession' and END_OF_MONTH = "31MAY2019"d;
run;

proc sql;
create table New_in as
select * from CM where accno not in (Select accno from LM); quit;

proc sql;
create table New_Out as
select * from LM where accno not in (Select accno from CM); quit;

Please advise.thanks

Regards,

RS

[PeterClemmensen]

What defines a "New In" and a "New Out"? Can you show us some example of what your data looks like?

[BaalaRaaji]

Below is the sample data...

Date_Perf	ACNO	REAL_STATUS_C	STATE_C	NEW_IN	New_OUT
May-18	123	Legal	NSW
Jun-18	123	Legal	NSW
Jul-18	123	Legal	NSW
Aug-18	123	Legal	NSW
Sep-18	123	Legal	NSW
Oct-18	123	Legal	NSW
Nov-18	123	Legal	NSW
Dec-18	123	Legal	NSW
Jan-19	123	Poss	NSW	1
Feb-19	123	Poss	NSW
Mar-19	123	Poss	NSW
Apr-19	123	Sold	NSW
May-19	123	Sold	NSW		1
May-18	234	Legal	VIC
Jun-18	234	Legal	VIC
Jul-18	234	Legal	VIC
Aug-18	234	Legal	VIC
Sep-18	234	Legal	VIC
Oct-18	234	Legal	VIC
Nov-18	234	Poss	VIC	1
Dec-18	234	Poss	VIC
Jan-19	234	Poss	VIC
Feb-19	234	Sold	VIC
Mar-19	234	Sold	VIC		1

I need to create flags based on accno and Real_Status_c

FOR EX: ACNO 123…from legal it has moved to possession- then New_in=1
FOR EX: ACNO 123…has sold in may19 but no record in june so the New_Out=1

comparsion between legal moved to either poss or sold…

Hope it helps..thanks

Regards,

RS

[PeterClemmensen]

Ok. Here is one way

data have;
input Date_Perf $ ACNO REAL_STATUS_C $ STATE_C $;
datalines; 
May-18 123 Legal NSW
Jun-18 123 Legal NSW
Jul-18 123 Legal NSW
Aug-18 123 Legal NSW
Sep-18 123 Legal NSW
Oct-18 123 Legal NSW
Nov-18 123 Legal NSW
Dec-18 123 Legal NSW
Jan-19 123 Poss NSW
Feb-19 123 Poss NSW
Mar-19 123 Poss NSW
Apr-19 123 Sold  NSW
May-19 123 Sold  NSW
May-18 234 Legal VIC
Jun-18 234 Legal VIC
Jul-18 234 Legal VIC
Aug-18 234 Legal VIC
Sep-18 234 Legal VIC
Oct-18 234 Legal VIC
Nov-18 234 Poss VIC
Dec-18 234 Poss VIC
Jan-19 234 Poss VIC
Feb-19 234 Sold  VIC
Mar-19 234 Sold  VIC
;

data want;
    set have;
    by ACNO;
    lagREAL=lag1(REAL_STATUS_C);
    if first.ACNO then lagREAL=" ";
    if REAL_STATUS_C="Poss" & lagREAL="Legal" then NEW_IN=1;
    if last.ACNO then NEW_OUT=1;
run;