I am trying to find accounts looping in again in the same reference code.

[Sandeep77]

Hi Experts,

I have created a table where previous account status was 133 reference code. I want to find the looping accounts in 133 reference code to know if I am dealing with same account numbers every month. Can you please the correct way to find out? I am stuck with my code as I don't know what should I add in to see if the same accounts are coming back in 133 reference code?

/* accounts moved out from 133 in 2022 */
Proc sql;
create table accounts_moved_out_of_133 as
select accountnumber,
AccountStatus,
PreviousAccountStatus,
EnteredAccountStatusDate as entered_in_current_ref_code
from dwhdw.dim_account
where PreviousAccountStatus like '133' ;
quit;

/* Have they ever been in 133 more than once */
proc sql; 
create table looping_in_133 as 

[PaigeMiller]

Please show us some sample data, as working SAS data step code (instructions). Please show us the desired output from this sample data.

[Sandeep77]

Hi, The below is the example of data when I checked the accounts that moved out of 133:

AccountNumber	AccountStatus	PreviousAccountStatus	entered_in_current_ref_code
318795598	2H01	133	26-Sep-22
402274427	005H	133	24-Sep-22
353938335	005H	133	25-Sep-22
266264266	005H	133	26-Sep-22

I want to find how many times the account number have moved out of 133 reference code. Is there a way to find it out? I am looking for all the accounts which are looping in 133 every month.

[PaigeMiller]

Hello, @Sandeep77 we're trying to help you, but you have to help us. Please do not ignore the request to provide data in a usable form, which I wrote as: "... as working SAS data step code (instructions)".

Also, please do not ignore my request to see the desired output.