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 */
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.
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 |
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".
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.
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;
@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.
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.
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;
@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.
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.
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.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.