Finding accounts which have been at least once in a category and are still in the same category

[Sandeep77]

Hi all,

Task is to find the all the debt_code (account number) which have been at least once in 133 repcode in the last one year and if they are still in the same repcode. For this, I first found the debt_code in 133 repcode. Then I found where the previous repcode was 133 in last one year. But I am not sure how to approach to find debt_code which have been in 133 and are still in 133. Can you guide what is the best way to get the solutions.

proc sql;
create table Accounts_In_133 AS
Select rep_code,
debt_code
From p2scflow.debt
Where rep_code LIKE '133';
quit;

[PeterClemmensen]

Can you provide some example data that resembles your problem? Makes it much easier to provide a usable code answer. 

[Sandeep77]

Below is the data for accounts in 133 repcode. It shows the repcode and debt_code. I am not sure how to approach further to find if the debt_code have been in 133 in the last year and if those debt_code are still in 133? 

data Accounts_In_133;
input rep_code :$4. debt_code :$9.;
datalines;
133 261094882
133 261138333
133 261149033
133 261185011
133 382118339
133 292320751
;
run;

[Patrick]

"Task is to find the all the debt_code (account number) which have been at least once in 133 repcode in the last one year and if they are still in the same repcode."

Any repcode that's currently in 133 also meets the requirement of have been at least once in 133.

If the requirement really is as you formulated it then you just need to check if the latest repcode for a specific debt_code is 133.

Your sample data is missing the date column - but let's assume there is a date column called dt then the SQL could look like:

proc sql;
  create table accounts_in_133 as
    select 
      debt_code
      ,max(dt) as latest_dt format=date9.
    from p2scflow.debt
    where rep_code='133'
    group by
      debt_code
    having max(dt)=dt
  ;
quit;