Accounts looping in the same category

[Sandeep77]

Hi Experts,

I have created a dataset which shows information about accounts in 133 rep code, accounts moved out of 133 rep code. I am trying to find how many times accounts have left 133 rep codes (is any accounts have entered 133 multiple times?) I have created the below code for accounts moved out of 133 which works fine but not sure about the next step to find if any of these accounts have entered or moved out of 133 more than once? Can you please assist?

/*  Accounts in 133 rep code currently */
proc sql;
create table accounts_in_133 as 
select rep_code,
debt_code 
From p2scflow.debt
Where rep_code LIKE '133'
Group By rep_code;
quit;

/* 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_repcode
from dwhdw.dim_account
where PreviousAccountStatus like '133' ;
quit;

/* Have they ever been in 133 more than once */

[PaigeMiller]

Please show us (a portion of) you data. Please provide the data as working SAS data step code, which you can type in yourself, or follow these instructions. Do not provide data any other way.

[Sandeep77]

Below is the sample of data after running the code:

Accountnumber	AccountStatus	PreviousAccountStatus	Entered_in_currrent_repcode
383708724	168	133	07-Mar-22
398721779	168	133	12-May-22
371263922	122	133	04-Jun-21
374781664	168	133	14-Mar-22
374748697	168	133	26-Jan-22

[PaigeMiller]

We have discussed all of this already here: https://communities.sas.com/t5/New-SAS-User/I-am-trying-to-find-accounts-looping-in-again-in-the-sam...

Let's not keep going over the same material. I want working SAS data step code, not a screen capture. That's what I said three times already, this paragraph is the fourth time. Also, we need the data at the beginning, in data set p2scflow.debt; not "after running the code". 

[Sandeep77]

Thank you for clarifying. I am not sure what is working SAS data step code? I have the data set p2scflow.debt which is huge. So can you recommend how to share it. I am not familiar with making a macro data.

[PaigeMiller]

I asked for "(a portion of)" that data set. We don't need the whole thing, just enough so that this illustrates the problem and so we can write code that works. (Your very brief data does not illustrate the problem)

I am not familiar with making a macro data.

Examples of working SAS data step code are at that link. The instructions for using this macro are at that link. Please read the instructions and ask specific questions if you're having problems. Here's another example of working SAS data step code that was typed in by @Reeza. 

[Sandeep77]

Thanks, I have copied a portion of the data and pasted in the code. Please check and let me know if that makes sense.

data Test;
input prrowid	dtSourceCreated	dtSourceModified	dtTargetCreated	dtTargetModified	debt_code	client_code	dt_cliref	dt_addref	dt_actmethod	dt_dalstrev	dt_nxtrev	op_code	dt_prohd	dt_curprostg	dt_nxtprostg	dt_status	dt_varcode	dt_vardate	dt_assignor	dt_assnaddr##1	dt_assnaddr##2	dt_assnaddr##3	dt_assnaddr##4	dt_assnpost	dt_assndat	court_type	court_code	dt_caseno	dt_issuedat	dt_sumval	dt_sumfees	dt_sumcosts	dt_servdat	dt_jmtprtdat	dt_jmtdat	dt_jmtval	dt_jmtcrt	dt_jmtcaseno	dt_warno	dt_oslastenf	dt_warcosts	dt_instfrq	dt_instalmnt	dt_balatstrt	dt_inststart	dt_instdat	dt_varprohd	dt_varprostg	dt_varstgnxt	dt_datlstr	dt_datlstv	dt_datlste	dt_datlsti	dt_datnxtr	dt_datnxtv	dt_datnxte	dt_datnxtd	dt_datnxti	dt_inactdat	dt_delaydays	dt_datdelay	dt_agentcod	dt_lstpayval	dt_lstpaydat	dt_pretx	dt_predtstat	dt_prefields	dt_prelstenf	dt_debtval	dt_datinstr	dt_curbal	dp_code	dt_debttype	dt_delstatus	dt_pdaftiss	dt_pdtoday	dt_pdtodaydt	dt_pdtodate	dt_fees	dt_costs	dt_interest	dt_lastint	dt_writeoff	dt_woreason	dt_target	dt_exception	dt_numexcns	dt_arltrdate	dt_commissn	rz_code	dt_princpaid	dt_intpaid	dt_feespaid	dt_costspaid	dt_n225flg	dt_landreg	dt_jcrt_type	dt_jcrt_code	dt_clsearch	dtsettlementje1	dt_spcflg	dt_jmtcosts	dt_logflg##1	dt_logflg##2	dt_logflg##3	dt_logflg##4	dt_logflg##5	dt_logflg##6	dt_logflg##7	dt_logflg##8	dt_logflg##9	dt_logflg##10	dt_charflg##1	dt_charflg##2	dt_charflg##3	dt_charflg##4	dt_charflg##5	dt_charflg##6	dt_charflg##7	dt_charflg##8	dt_charflg##9	dt_charflg##10	dt_jmttype	dt_defence	dt_dailyrate	dt_other_csts	dt_intsums	dt_intjmt	dt_intacc	dt_hearingdt	dt_hearingtm	dt_dailyrat2	dt_warfee	dt_overpaid	dt_princbal	dt_intbal	dt_feesbal	dt_costsbal	dt_dtzerobal	dt_tmzerobal	dt_statspd_princ	dt_statspd_int	dt_statspd_fees	dt_statspd_costs	dt_statspd_over	dt_overpdusr	dt_overpdclt	matter_type	dt_mtrdesc	dt_statsyr	dt_statsmth	dt_arrears	dt_takeon	dt_hcrt_type	dt_hcrt_code	dt_ccbc	dt_transfer	dt_partenf	dt_adtypes	dt_inst_alert	rep_code	dt_rep_init_date	dt_settlement	dt_method_a	dt_actiontm	dt_last_rep	dt_in_skip	dt_last_cais	dt_addr_changed	dt_cbal_changed	dt_instint	dt_alloc_dt	dt_acknowledged	dtextrapofc##1	dtextrapofc##2	dtextrapofc##3	currencycode	dtenfdate	dtexcnstage	dtexcntype	dtexcnrevert	dtcaisdelete	dtcaisdeldate	dtcaisqualified	caisstatus	dtlstinstfrq	dtlstinstamt	dtlstinststart	dtlstinstwiped	dtlstinstdefflg	dtlstinstarrears	dtpdsincelstinst	dtconsolidated	masternumber	dtinstructedage	dt_precurbal	dt_precbnodecs	dtcondition	dtconditiondt	countrycode	dtexportaddr	dtexportcbal	dtexportok	dtbedrag	dtaltcurr	dt_precboperator	dtpartpayment	dt_prerepcode	dt_premattertype	dtarrcnt	dtdefcnt	pspreprincbaltest	psprebalcurr	pspreobnodecs	dt_dateflg##1	dt_dateflg##2	dt_dateflg##3	dt_dateflg##4	dt_dateflg##5	dt_dateflg##6	dt_dateflg##7	dt_dateflg##8	dt_dateflg##9	dt_dateflg##10	dtPrevLitigated	dtbehaviourscore	dt_enfflg	dttelcoossegment	strategyid	litigationconsideration	segment	dcaid	dtcompany	DiscountPercentage	SatisfactionDate	lastpaymentdate	lastpaymenttrancode	lastpaymentvalue	totalvaluepaidtolowell	lastrpcdate	prevlastpaymentdate	prevlastpaymenttrancode	prevlastpaymentvalue	ccurrentchasestrategy	dlastreviewdate	tcfrecordcreated	tcfrecordmodified	lHasWebAccount	tWebAccountRegistered	clitigationstatus	npreferredjudgmentinstallment	crequestedpreclaimstrategy	crequestedclaimstrategy	crequestedjudgmentstrategy	clocalcourttype	clocalcourtcode	cjudgmenttype	cjudgmentstatus	cclaimstatus	nclaimasadmitted	dt_prevnxtprostg	djudgmentfirstpayment	cclaimnumber	dclaimissue	dclaimservice	nclaimfee	ldefaultnoticesend	ddefaultnoticedate	clowellcaisstatus	ienforcementscore	ilitigationscore	cspecialinstruction	cpreenforcementcontactstrategy	ddiscountexpiry	lfcoheld	cproposedfreq	lactivecounterclaim	clitigationsource	dstb	cwarrantstatus	cwarrantnumber	dwarrantissue	nwarrantfee	cwarrantcourtcode	cwarrantcourtname	lnondefaulted	ddefaulted	dlitigationorderdate	clitigationordertype	cclaimparticulars##1	cclaimparticulars##2	cclaimparticulars##3	cclaimparticulars##4	cclaimparticulars##5	cclaimparticulars##6	cclaimparticulars##7	cclaimparticulars##8	cclaimparticulars##9	cclaimparticulars##10	cclaimparticulars##11	cclaimparticulars##12	cclaimparticulars##13	cclaimparticulars##14	cclaimparticulars##15	cclaimparticulars##16	cclaimparticulars##17	cclaimparticulars##18	cclaimparticulars##19	cclaimparticulars##20	cclaimparticulars##21	cclaimparticulars##22	cclaimparticulars##23	cclaimparticulars##24	packetid	packetseq	lnonlitigation	ijointaccountnumber	itotaljointaccounts	dtcofinaldate	iquerygroups	iopenqueries	iopentasks	iqmsblazeinterest	lnonlowellmissingpayment	cdefence	nlastcalcjudgearrears	ndiscountamount	lhasprimarylegaladdressee	lignoreinsolvency	dlastfees	dprevfees	naccepteddiscount	crepcode9source	crepcode9action	drepcode9date	crepcode9opcode	dlastcosts	dprevcosts	cadditionalsupportflagtype	dadditionalsupportflagexpirydate	dt_addref2	dt_addref3	dt_addref4	dt_addref5	dt_addref6	dt_addref7	dt_addref8	dt_addref9	lastinboundrpcdate	dadditionalsupportflagcreated	firstpaymentdate	firstpaymenttrancode	firstpaymentvalue	dtRetainedCollections	insolvencystopaccount	dlastclaimnumberupdate	ldiallable	coverridesector	llsllink	iversion	iclientseq	cjointaccounttype	csasjointaccountref	ccraref	cpriorityflag	dtbetkla	ljmtexpired	iclientaccountid	dbalancechange	dpreviousbalancechange	iclaimantid	icasesourceid	iinstructionbatchid	ccasetype	lconsolidated	corigmattertype	dfirstfee	dfirstcost	lcrasubaccountoverride	lhasapp	icaisadjustment	icaisatbookon;
datalines;
0x0000000006adb3c3	28MAR2014:00:00:00.000	29MAR2022:16:51:09.000	28MAR2014:00:00:00.000	29MAR2022:17:33:11.203	158126219	WILLIAMS009	U0757494		1	30NOV2017:00:00:00.000	.	COLLECT	CLOSE4	CLOSE4999	*********	3		.							.	C			.	0	0	0	.	.	.	0				0	0		0	0	.	.				31OCT2017:00:00:00.000	.	.	.	.	.	.	.	.	31OCT2017:00:00:00.000	5	14JAN2016:00:00:00.000		0	.		0		0	251.97	28MAR2014:00:00:00.000	0	JDJAC	D	D	0	0	.	0	0	0	0	.	251.97	4	D		0	.	0		0	0	0	0	0				JDWILLIAMS	0	0	0	0	0	0	0	0	0	0	0	0	0											0	0	0	0	0	0	0	.	0	0	0	0	0	0	0	0	31OCT2017:00:00:00.000	00:33:31	0	0	0	0	251.97	0	251.97	STB	Williams009	14	3	0	0			0		0		0	904	31OCT2017:00:00:00.000	0		0	904	0	16AUG2014:00:00:00.000	0	1	0	.	0				GBP	.	0	0	0	0	.	1	8		0	.	.	0	0	0	0	14629203	0	0	0	CLOSED	31OCT2017:00:00:00.000	UK	1	1	1	2315327			0			0	0	0	D	0	.	.	.	.	.	.	.	.	.	.	0	0		0	216	0		0	0	75	.	.		0	0	.	.		0		.	.	29MAR2022:16:51:09.033	0	.		0									0	CLOSE4999	.		.	.	0	0	.		0	0			23AUG2017:00:00:00.000	0		0		30OCT2017:00:00:00.000			.	0			0	.	.																										0	0	1	0	0	.	0	0	0	0	0		0	188.98	0	0	.	.	0	T	MO1573	30OCT2017:00:00:00.000	AAM	.	.		.									.	.	.		0	0	0	.	0		0	1	1	S	BACKFILL: 20180513	158126219		0	0	26904832	.	.	0	0	0		0		.	.	0	0	0	0
0x0000000006adb3c4	28MAR2014:00:00:00.000	29MAR2022:16:51:09.000	28MAR2014:13:33:23.907	29MAR2022:17:33:11.600	158126227	WILLIAMS009	F5442868		1	.	24JUL2021:00:00:00.000	COLLECT	REVIEW		REVIEW999	3		.							.	C			.	0	0	0	.	.	.	0				0	0		0	0	.	.				.	.	.	.	24JUL2021:00:00:00.000	.	.	.	.	30OCT2018:00:00:00.000	0	.		0	.		0		0	223.93	28MAR2014:00:00:00.000	223.93	SCFASW	D	L	0	0	.	0	0	0	0	.	0	0	D		0	.	0		0	0	0	0	0				JDWILLIAMS	0	0	0	0	0	0	0	0	0	0	0	0	0											0	0	0	0	0	0	0	.	0	0	0	0	223.93	0	0	0	.		0	0	0	0	0	0	0	STB	Williams009	14	3	0	0			0		0		0	005H	30OCT2018:00:00:00.000	0		0	157	0	26OCT2014:00:00:00.000	1	0	0	.	0				GBP	.	0	0	0	0	.	1	8		0	.	.	0	0	0	0	14629204	0	0	0	OPEN	28MAR2014:00:00:00.000	UK	1	1	1	2315327			0			0	0	0	D	0	.	.	.	.	.	.	.	.	.	.	0	0		0	0	0		0	0	0	.	.		0	0	.	.		0		.	.	29MAR2022:16:51:09.033	0	.		0									0	TRACE 999	.		.	.	0	0	.		0	0			.	0		0		30OCT2018:00:00:00.000			.	0			0	.	.																										0	0	1	0	0	.	0	0	0	0	0		0	0	0	0	.	.	0			.		.	.		.									.	.	.		0	0	0	.	0		0	1	1	S	BACKFILL: 20180513	158126227		0	0	25585457	.	.	0	0	0		0		.	.	0	0	0	0
0x0000000006adb3c5	28MAR2014:00:00:00.000	29MAR2022:16:51:09.000	28MAR2014:13:33:24.467	29MAR2022:17:33:37.120	158126235	WILLIAMS009	Q5022372		1	19DEC2014:00:00:00.000	24JUL2021:00:00:00.000	COLLECT	REVIEW	MOSVGE999	REVIEW999	3		.							.	C			.	0	0	0	.	.	.	0				0	0		0	0	.	.				19DEC2014:00:00:00.000	.	.	.	24JUL2021:00:00:00.000	.	.	.	.	30OCT2018:00:00:00.000	0	.		0	.		0		0	230.4	28MAR2014:00:00:00.000	230.4	SCAMB	D	L	0	0	.	0	0	0	0	.	0	0	D		0	.	0		0	0	0	0	0				JDWILLIAMS	0	0	0	0	0	0	0	0	0	0	0	0	0											0	0	0	0	0	0	0	.	0	0	0	0	230.4	0	0	0	.		0	0	0	0	0	0	0	STB	Williams009	14	3	0	0			0		0		0	005H	30OCT2018:00:00:00.000	0		0	157	0	26OCT2014:00:00:00.000	1	0	0	.	0				GBP	.	0	0	0	0	.	1	8		0	.	.	0	0	0	0	14629205	0	0	0	OPEN	28MAR2014:00:00:00.000	UK	1	1	1	2315327			0			0	0	0	D	0	.	.	.	.	.	.	.	.	.	.	0	0		0	201	0		0	0	0	.	.		0	0	.	.		0		.	.	29MAR2022:16:51:09.033	0	.		0									0	TRACE 999	.		.	.	0	0	.		0	0			.	0		0		30OCT2018:00:00:00.000			.	0			0	.	.																										0	0	1	0	0	.	0	0	0	0	0		0	0	0	0	.	.	0			.		.	.		.									.	.	.		0	0	0	.	0		0	1	1	S	BACKFILL: 20180513	158126235		0	0	26548714	.	.	0	0	0		0		.	.	0	0	0	0;
run;

[ballardw]

---

@Sandeep77 wrote:

Thanks, I have copied a portion of the data and pasted in the code. Please check and let me know if that makes sense.

data Test;
input prrowid	dtSourceCreated	dtSourceModified	dtTargetCreated	dtTargetModified	debt_code	client_code	dt_cliref	dt_addref	dt_actmethod	dt_dalstrev	dt_nxtrev	op_code	dt_prohd	dt_curprostg	dt_nxtprostg	dt_status	dt_varcode	dt_vardate	dt_assignor	dt_assnaddr##1	dt_assnaddr##2	dt_assnaddr##3	dt_assnaddr##4	dt_assnpost	dt_assndat	court_type	court_code	dt_caseno	dt_issuedat	dt_sumval	dt_sumfees	dt_sumcosts	dt_servdat	dt_jmtprtdat	dt_jmtdat	dt_jmtval	dt_jmtcrt	dt_jmtcaseno	dt_warno	dt_oslastenf	dt_warcosts	dt_instfrq	dt_instalmnt	dt_balatstrt	dt_inststart	dt_instdat	dt_varprohd	dt_varprostg	dt_varstgnxt	dt_datlstr	dt_datlstv	dt_datlste	dt_datlsti	dt_datnxtr	dt_datnxtv	dt_datnxte	dt_datnxtd	dt_datnxti	dt_inactdat	dt_delaydays	dt_datdelay	dt_agentcod	dt_lstpayval	dt_lstpaydat	dt_pretx	dt_predtstat	dt_prefields	dt_prelstenf	dt_debtval	dt_datinstr	dt_curbal	dp_code	dt_debttype	dt_delstatus	dt_pdaftiss	dt_pdtoday	dt_pdtodaydt	dt_pdtodate	dt_fees	dt_costs	dt_interest	dt_lastint	dt_writeoff	dt_woreason	dt_target	dt_exception	dt_numexcns	dt_arltrdate	dt_commissn	rz_code	dt_princpaid	dt_intpaid	dt_feespaid	dt_costspaid	dt_n225flg	dt_landreg	dt_jcrt_type	dt_jcrt_code	dt_clsearch	dtsettlementje1	dt_spcflg	dt_jmtcosts	dt_logflg##1	dt_logflg##2	dt_logflg##3	dt_logflg##4	dt_logflg##5	dt_logflg##6	dt_logflg##7	dt_logflg##8	dt_logflg##9	dt_logflg##10	dt_charflg##1	dt_charflg##2	dt_charflg##3	dt_charflg##4	dt_charflg##5	dt_charflg##6	dt_charflg##7	dt_charflg##8	dt_charflg##9	dt_charflg##10	dt_jmttype	dt_defence	dt_dailyrate	dt_other_csts	dt_intsums	dt_intjmt	dt_intacc	dt_hearingdt	dt_hearingtm	dt_dailyrat2	dt_warfee	dt_overpaid	dt_princbal	dt_intbal	dt_feesbal	dt_costsbal	dt_dtzerobal	dt_tmzerobal	dt_statspd_princ	dt_statspd_int	dt_statspd_fees	dt_statspd_costs	dt_statspd_over	dt_overpdusr	dt_overpdclt	matter_type	dt_mtrdesc	dt_statsyr	dt_statsmth	dt_arrears	dt_takeon	dt_hcrt_type	dt_hcrt_code	dt_ccbc	dt_transfer	dt_partenf	dt_adtypes	dt_inst_alert	rep_code	dt_rep_init_date	dt_settlement	dt_method_a	dt_actiontm	dt_last_rep	dt_in_skip	dt_last_cais	dt_addr_changed	dt_cbal_changed	dt_instint	dt_alloc_dt	dt_acknowledged	dtextrapofc##1	dtextrapofc##2	dtextrapofc##3	currencycode	dtenfdate	dtexcnstage	dtexcntype	dtexcnrevert	dtcaisdelete	dtcaisdeldate	dtcaisqualified	caisstatus	dtlstinstfrq	dtlstinstamt	dtlstinststart	dtlstinstwiped	dtlstinstdefflg	dtlstinstarrears	dtpdsincelstinst	dtconsolidated	masternumber	dtinstructedage	dt_precurbal	dt_precbnodecs	dtcondition	dtconditiondt	countrycode	dtexportaddr	dtexportcbal	dtexportok	dtbedrag	dtaltcurr	dt_precboperator	dtpartpayment	dt_prerepcode	dt_premattertype	dtarrcnt	dtdefcnt	pspreprincbaltest	psprebalcurr	pspreobnodecs	dt_dateflg##1	dt_dateflg##2	dt_dateflg##3	dt_dateflg##4	dt_dateflg##5	dt_dateflg##6	dt_dateflg##7	dt_dateflg##8	dt_dateflg##9	dt_dateflg##10	dtPrevLitigated	dtbehaviourscore	dt_enfflg	dttelcoossegment	strategyid	litigationconsideration	segment	dcaid	dtcompany	DiscountPercentage	SatisfactionDate	lastpaymentdate	lastpaymenttrancode	lastpaymentvalue	totalvaluepaidtolowell	lastrpcdate	prevlastpaymentdate	prevlastpaymenttrancode	prevlastpaymentvalue	ccurrentchasestrategy	dlastreviewdate	tcfrecordcreated	tcfrecordmodified	lHasWebAccount	tWebAccountRegistered	clitigationstatus	npreferredjudgmentinstallment	crequestedpreclaimstrategy	crequestedclaimstrategy	crequestedjudgmentstrategy	clocalcourttype	clocalcourtcode	cjudgmenttype	cjudgmentstatus	cclaimstatus	nclaimasadmitted	dt_prevnxtprostg	djudgmentfirstpayment	cclaimnumber	dclaimissue	dclaimservice	nclaimfee	ldefaultnoticesend	ddefaultnoticedate	clowellcaisstatus	ienforcementscore	ilitigationscore	cspecialinstruction	cpreenforcementcontactstrategy	ddiscountexpiry	lfcoheld	cproposedfreq	lactivecounterclaim	clitigationsource	dstb	cwarrantstatus	cwarrantnumber	dwarrantissue	nwarrantfee	cwarrantcourtcode	cwarrantcourtname	lnondefaulted	ddefaulted	dlitigationorderdate	clitigationordertype	cclaimparticulars##1	cclaimparticulars##2	cclaimparticulars##3	cclaimparticulars##4	cclaimparticulars##5	cclaimparticulars##6	cclaimparticulars##7	cclaimparticulars##8	cclaimparticulars##9	cclaimparticulars##10	cclaimparticulars##11	cclaimparticulars##12	cclaimparticulars##13	cclaimparticulars##14	cclaimparticulars##15	cclaimparticulars##16	cclaimparticulars##17	cclaimparticulars##18	cclaimparticulars##19	cclaimparticulars##20	cclaimparticulars##21	cclaimparticulars##22	cclaimparticulars##23	cclaimparticulars##24	packetid	packetseq	lnonlitigation	ijointaccountnumber	itotaljointaccounts	dtcofinaldate	iquerygroups	iopenqueries	iopentasks	iqmsblazeinterest	lnonlowellmissingpayment	cdefence	nlastcalcjudgearrears	ndiscountamount	lhasprimarylegaladdressee	lignoreinsolvency	dlastfees	dprevfees	naccepteddiscount	crepcode9source	crepcode9action	drepcode9date	crepcode9opcode	dlastcosts	dprevcosts	cadditionalsupportflagtype	dadditionalsupportflagexpirydate	dt_addref2	dt_addref3	dt_addref4	dt_addref5	dt_addref6	dt_addref7	dt_addref8	dt_addref9	lastinboundrpcdate	dadditionalsupportflagcreated	firstpaymentdate	firstpaymenttrancode	firstpaymentvalue	dtRetainedCollections	insolvencystopaccount	dlastclaimnumberupdate	ldiallable	coverridesector	llsllink	iversion	iclientseq	cjointaccounttype	csasjointaccountref	ccraref	cpriorityflag	dtbetkla	ljmtexpired	iclientaccountid	dbalancechange	dpreviousbalancechange	iclaimantid	icasesourceid	iinstructionbatchid	ccasetype	lconsolidated	corigmattertype	dfirstfee	dfirstcost	lcrasubaccountoverride	lhasapp	icaisadjustment	icaisatbookon;
datalines;
0x0000000006adb3c3	28MAR2014:00:00:00.000	29MAR2022:16:51:09.000	28MAR2014:00:00:00.000	29MAR2022:17:33:11.203	158126219	WILLIAMS009	U0757494		1	30NOV2017:00:00:00.000	.	COLLECT	CLOSE4	CLOSE4999	*********	3		.							.	C			.	0	0	0	.	.	.	0				0	0		0	0	.	.				31OCT2017:00:00:00.000	.	.	.	.	.	.	.	.	31OCT2017:00:00:00.000	5	14JAN2016:00:00:00.000		0	.		0		0	251.97	28MAR2014:00:00:00.000	0	JDJAC	D	D	0	0	.	0	0	0	0	.	251.97	4	D		0	.	0		0	0	0	0	0				JDWILLIAMS	0	0	0	0	0	0	0	0	0	0	0	0	0											0	0	0	0	0	0	0	.	0	0	0	0	0	0	0	0	31OCT2017:00:00:00.000	00:33:31	0	0	0	0	251.97	0	251.97	STB	Williams009	14	3	0	0			0		0		0	904	31OCT2017:00:00:00.000	0		0	904	0	16AUG2014:00:00:00.000	0	1	0	.	0				GBP	.	0	0	0	0	.	1	8		0	.	.	0	0	0	0	14629203	0	0	0	CLOSED	31OCT2017:00:00:00.000	UK	1	1	1	2315327			0			0	0	0	D	0	.	.	.	.	.	.	.	.	.	.	0	0		0	216	0		0	0	75	.	.		0	0	.	.		0		.	.	29MAR2022:16:51:09.033	0	.		0									0	CLOSE4999	.		.	.	0	0	.		0	0			23AUG2017:00:00:00.000	0		0		30OCT2017:00:00:00.000			.	0			0	.	.																										0	0	1	0	0	.	0	0	0	0	0		0	188.98	0	0	.	.	0	T	MO1573	30OCT2017:00:00:00.000	AAM	.	.		.									.	.	.		0	0	0	.	0		0	1	1	S	BACKFILL: 20180513	158126219		0	0	26904832	.	.	0	0	0		0		.	.	0	0	0	0
0x0000000006adb3c4	28MAR2014:00:00:00.000	29MAR2022:16:51:09.000	28MAR2014:13:33:23.907	29MAR2022:17:33:11.600	158126227	WILLIAMS009	F5442868		1	.	24JUL2021:00:00:00.000	COLLECT	REVIEW		REVIEW999	3		.							.	C			.	0	0	0	.	.	.	0				0	0		0	0	.	.				.	.	.	.	24JUL2021:00:00:00.000	.	.	.	.	30OCT2018:00:00:00.000	0	.		0	.		0		0	223.93	28MAR2014:00:00:00.000	223.93	SCFASW	D	L	0	0	.	0	0	0	0	.	0	0	D		0	.	0		0	0	0	0	0				JDWILLIAMS	0	0	0	0	0	0	0	0	0	0	0	0	0											0	0	0	0	0	0	0	.	0	0	0	0	223.93	0	0	0	.		0	0	0	0	0	0	0	STB	Williams009	14	3	0	0			0		0		0	005H	30OCT2018:00:00:00.000	0		0	157	0	26OCT2014:00:00:00.000	1	0	0	.	0				GBP	.	0	0	0	0	.	1	8		0	.	.	0	0	0	0	14629204	0	0	0	OPEN	28MAR2014:00:00:00.000	UK	1	1	1	2315327			0			0	0	0	D	0	.	.	.	.	.	.	.	.	.	.	0	0		0	0	0		0	0	0	.	.		0	0	.	.		0		.	.	29MAR2022:16:51:09.033	0	.		0									0	TRACE 999	.		.	.	0	0	.		0	0			.	0		0		30OCT2018:00:00:00.000			.	0			0	.	.																										0	0	1	0	0	.	0	0	0	0	0		0	0	0	0	.	.	0			.		.	.		.									.	.	.		0	0	0	.	0		0	1	1	S	BACKFILL: 20180513	158126227		0	0	25585457	.	.	0	0	0		0		.	.	0	0	0	0
0x0000000006adb3c5	28MAR2014:00:00:00.000	29MAR2022:16:51:09.000	28MAR2014:13:33:24.467	29MAR2022:17:33:37.120	158126235	WILLIAMS009	Q5022372		1	19DEC2014:00:00:00.000	24JUL2021:00:00:00.000	COLLECT	REVIEW	MOSVGE999	REVIEW999	3		.							.	C			.	0	0	0	.	.	.	0				0	0		0	0	.	.				19DEC2014:00:00:00.000	.	.	.	24JUL2021:00:00:00.000	.	.	.	.	30OCT2018:00:00:00.000	0	.		0	.		0		0	230.4	28MAR2014:00:00:00.000	230.4	SCAMB	D	L	0	0	.	0	0	0	0	.	0	0	D		0	.	0		0	0	0	0	0				JDWILLIAMS	0	0	0	0	0	0	0	0	0	0	0	0	0											0	0	0	0	0	0	0	.	0	0	0	0	230.4	0	0	0	.		0	0	0	0	0	0	0	STB	Williams009	14	3	0	0			0		0		0	005H	30OCT2018:00:00:00.000	0		0	157	0	26OCT2014:00:00:00.000	1	0	0	.	0				GBP	.	0	0	0	0	.	1	8		0	.	.	0	0	0	0	14629205	0	0	0	OPEN	28MAR2014:00:00:00.000	UK	1	1	1	2315327			0			0	0	0	D	0	.	.	.	.	.	.	.	.	.	.	0	0		0	201	0		0	0	0	.	.		0	0	.	.		0		.	.	29MAR2022:16:51:09.033	0	.		0									0	TRACE 999	.		.	.	0	0	.		0	0			.	0		0		30OCT2018:00:00:00.000			.	0			0	.	.																										0	0	1	0	0	.	0	0	0	0	0		0	0	0	0	.	.	0			.		.	.		.									.	.	.		0	0	0	.	0		0	1	1	S	BACKFILL: 20180513	158126235		0	0	26548714	.	.	0	0	0		0		.	.	0	0	0	0;
run;

---

Actually that does not make sense. No informats to read the data properly, invalid variable names (# is not allowed in SAS names by default). That data step throws so many errors I'm not even going to try t o list them all.

Which variables are actually needed fro the process? Only include those. I still see no definition of "moved out" or "have entered" that should be described in terms of values of variables.

[PaigeMiller]

How would this data, if it could be read properly, indicate the accounts looping in the same category? I doubt 3 records is enough for us to write code from, and the example data needs to illustrate the problem (which I don't think 3 records is enough), and illustrate all possible problem situations that appear in the data (again I doubt 3 records is enough).

Also, originally you just showed us 4 columns, is that all we need? Or do we need the dozens of columns in your latest data? If we only need 4 columns, just show us the 4 columns.

[Sandeep77]

Okay. Apologies for the mistake. I have now selected only those columns which I need. I have changed enteredaccountstatusdate to entered_in_current_repcode as you can see in my code. All these 4 column information is taken from the dwhdw.dim_account table. Please see the portion of data again and thanks a lot for helping. (Debt_code is accountnumber). Thanks

data Test;
input Debt_code	AccountStatus	PreviousAccountStatus	EnteredAccountStatusDate
;
datalines;
158126219	2H08	2H07	06-Oct-22
158126227	899	892H	07-Jul-22
158126235	901	LBAL	01-Oct-19
21653356	005H	168	22-Sep-22
21653357	005H	168	22-Sep-22
21653358	755	780	12-Aug-20
;
run;

[Kurt_Bremser]

---

@Sandeep77 wrote:

Okay. Apologies for the mistake. I have now selected only those columns which I need. I have changed enteredaccountstatusdate to entered_in_current_repcode as you can see in my code. All these 4 column information is taken from the dwhdw.dim_account table. Please see the portion of data again and thanks a lot for helping. (Debt_code is accountnumber). Thanks

data Test;
input Debt_code	AccountStatus	PreviousAccountStatus	EnteredAccountStatusDate
;
datalines;
158126219	2H08	2H07	06-Oct-22
158126227	899	892H	07-Jul-22
158126235	901	LBAL	01-Oct-19
21653356	005H	168	22-Sep-22
21653357	005H	168	22-Sep-22
21653358	755	780	12-Aug-20
;
run;

---

Are you deliberately trying to insult us?

If not, correct your code so that it works and creates a dataset.

We (the helpers here) take great pride in posting code which is tested, so please do the same.

[PaigeMiller]

How is this data related to your original problem description?

I am trying to find how many times accounts have left 133 rep codes (is any accounts have entered 133 multiple times?) I have created the below code for accounts moved out of 133 which works fine but not sure about the next step to find if any of these accounts have entered or moved out of 133 more than once? 

You don't show any "rep codes" and none of these seem to have 133 in the data set. We need a data set that illustrates the problem and covers all the likely situations that may occurs. We have asked for this several times now. 

[ballardw]

Without some actual data as in from both sets and a third example showing the desired output I cannot tell what you mean by "moved out" or "have entered". You are using some jargon that is very explicit to your problem without defining it to any one. If we don't know what it is we can't count it. And without a small manually worked example we may not be able to tell what you expect as a result.

[Patrick]

Just assuming an answer based on code as provided.

Syntax testing or verification if the code returns the desired result not possible due to insufficient information and insufficient sample data provided.

/*  Accounts in 133 rep code currently */
proc sql;
  create table accounts_in_133 as 
    select 
      rep_code,
      debt_code,
      count(*) as n_times_in_133
    From p2scflow.debt
      Where rep_code LIKE '133'
    group by 
      rep_code, 
      debt_code
    ;
quit;

/* 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_repcode,
      count(*) as n_times_out_133
    from dwhdw.dim_account
      where PreviousAccountStatus like '133'
    group by 
      accountnumber,
      AccountStatus,
      PreviousAccountStatus
    ;
quit;

[Reeza]

Here are instructions on how to create a data step, but please make sure to test that it works properly. Some variables are not created correctly sometimes, there are other macros/examples out there but a bit more complex IMO.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...