BookmarkSubscribeRSS Feed
Sandeep77
Lapis Lazuli | Level 10

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;
3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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

Sandeep77
Lapis Lazuli | Level 10

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
Opal | Level 21

"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;

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 3 replies
  • 498 views
  • 0 likes
  • 3 in conversation