How to subset a dataset when multiple records exist per subject

Mirisage — Wed, 13 Jun 2012 03:28:50 GMT

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

Re: How to subset a dataset when multiple records exist per subject

Ksharp — Wed, 13 Jun 2012 05:05:22 GMT

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

Re: How to subset a dataset when multiple records exist per subject

Mirisage — Wed, 13 Jun 2012 13:49:33 GMT

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

topic Re: How to subset a dataset when multiple records exist per subject in SAS Procedures

How to subset a dataset when multiple records exist per subject

Re: How to subset a dataset when multiple records exist per subject

Re: How to subset a dataset when multiple records exist per subject