BookmarkSubscribeRSS Feed
Mirisage
Obsidian | Level 7

Hi SAS Community,

I need to separate the account numbers of the attached file with this condition.

if Account_number ^= "0" and transit="Retail" and product in ("Personal Loan", "Personal OD", "Res. Mortgage");

The complexity is some accounts satisfy this condition only in certain months of the account's life. For e.g. account_number  No. 1111111111 satisfies the condition only in months of 31OCT2010 and 31JAN2012.

But I need only accounts those satisfy our condition across their entire life cycle.

Only account 44444444 satisfies this condition. So, a code is required to separate only the four records belong to account 44444444.

I have tried the “subsetting if” like below but obviously it is not clever enough.

data want;

     set have;

     if Account_number ^= "0" and transit="Retail" and product in ("Personal Loan", "Personal OD", "Res. Mortgage");

run;

Could anyone help me.

Mirisage

Thank you

Mirisage

2 REPLIES 2
Ksharp
Super User

It looks like you are trying to filter obs and also want to filter groups. SQL is a good tool.

data have;
informat current_date date9.;
input current_date account_number $ 13-22 transit $ 25-32 product $ 37-49  Arrears_Band $ 53-59;
format current_date date9.;
datalines;
28FEB2010   0           Retail      Personal Loan   Current
31MAR2010   0           Retail      Personal Loan   Current
31OCT2010   1111111111  Retail      Personal Loan   1 - 30 
30NOV2010   1111111111  Small Bu    Personal Loan   Current
31DEC2010   1111111111  Small Bu    Personal Loan   Current
31JAN2011   1111111111  Small Bu    Personal Loan   Current
28FEB2011   1111111111  Small Bu    Personal Loan   Current
31MAR2011   1111111111  Small Bu    Personal Loan   Current
30APR2011   1111111111  Small Bu    Personal Loan   Current
31MAY2011   1111111111  Wealth      Personal Loan   Current
30JUN2011   1111111111  Small Bu    Personal Loan   Current
31JUL2011   1111111111  Small Bu    Personal Loan   1 - 30
31AUG2011   1111111111  Small Bu    Personal Loan   Current
30SEP2011   1111111111  Small Bu    Personal Loan   Current
31OCT2011   1111111111  Small Bu    Personal Loan   Current
30NOV2011   1111111111  Small Bu    Personal Loan   Current
31DEC2011   1111111111  Retail      Corporate       Current
31JAN2012   1111111111  Retail      Personal Loan   Current
28FEB2010   2222222     Wealth      Personal OD     Current
31MAR2010   2222222     Wealth      Personal OD     Current
30APR2010   2222222     Wealth      Personal OD         Current
31MAY2010   2222222     Wealth      Personal OD         Current
30JUN2010   2222222     Wealth      Personal OD         Current
31JUL2010   2222222     Wealth      Personal OD         Current
31AUG2010   2222222     Wealth      Personal OD         Current
30SEP2010   2222222     Wealth      Personal OD         Current
30NOV2010   2222222     Retail      Personal OD         Current
31DEC2010   2222222     Retail      Personal OD         Current
31JAN2011   2222222     Retail      Personal OD         Current
28FEB2011   2222222     Wealth      Personal OD         Current
31MAR2011   2222222     Wealth      Personal OD         Current
30APR2011   2222222     Retail      Personal OD         Current
31MAY2011   2222222     Retail      Personal OD         Current
30JUN2011   2222222     Retail      Personal OD         Current
31JUL2011   2222222     Retail      Personal OD         Current
31AUG2011   2222222     Retail      Personal OD         Current
30SEP2011   2222222     Small Bu    Personal OD     Current
28FEB2010   3333333333  Wealth      Corporate       Current
31MAR2010   3333333333  Wealth      Corporate       Current
30APR2010   3333333333  Wealth      Corporate       Current
31MAY2010   3333333333  Retail      Res. Mortgage   Current
30JUN2010   3333333333  Retail      Res. Mortgage   Current
31JUL2010   3333333333  Retail      Res. Mortgage   Current
31AUG2010   3333333333  Retail      Res. Mortgage   Current
30SEP2010   3333333333  Retail      Res. Mortgage   Current
31OCT2010   3333333333  Retail      Res. Mortgage   Current
30NOV2010   3333333333  Retail      Res. Mortgage   Current
31DEC2010   3333333333  Retail      Res. Mortgage   Current
31JAN2011   3333333333  Retail      Res. Mortgage   Current
28FEB2011   3333333333  Retail      Res. Mortgage   Current
31MAR2011   3333333333  Retail      Res. Mortgage   Current
30APR2011   3333333333  Retail      Res. Mortgage   Current
31MAY2011   3333333333  Retail      Res. Mortgage   Current
30JUN2011   3333333333  Retail      Res. Mortgage   Current
31JUL2011   3333333333  Retail      Res. Mortgage   Current
31AUG2011   3333333333  Retail      Res. Mortgage   Current
30SEP2011   3333333333  Retail      Res. Mortgage   Current
31OCT2011   4444444444  Retail      Personal Loan   Current
30NOV2011   4444444444  Retail      Res. Mortgage   Current
31DEC2011   4444444444  Retail      Personal OD     Current
31JAN2012   4444444444  Retail      Personal OD     Current
;
run;

proc sql;
create table want as
 select *
  from have 
   where Account_number ^= "0"
    group by  Account_number
     having count(*) eq  sum(transit="Retail" and product in ("Personal Loan", "Personal OD", "Res. Mortgage")) ;
quit;







Ksharp

Mirisage
Obsidian | Level 7

Hi Ksharp,

Thank you very much for this help.

Your code anabled me to identify shortcomings of "subsetting if" approach in this example.

Thanks again!

Mirisage


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 680 views
  • 0 likes
  • 2 in conversation