Recoding a variable?

[Mirisage]

Hi Community,

I have a data set like below related to a real business problem.

data t;

input      customer_ID    PPS_BAL6     PPS_BAL0;

datalines;

1    10      8        /*Account balance reduced, I need them*/

2     -500  -1000 /*Account balance reduced but I do not need this kind of customers 'cos they have negative balances in both periods*/

3    10     10       /*Account balance "unchanged", I need them*/

4     0      0        /*Account balance "unchanged" but I do not need this kind of customers 'cos they have zero balances in both periods*/

5   -500   -500   /*Account balance "unchanged" but I do not need this kind of customers 'cos they have negative balances in both periods*/

6    10    12      /*Account balance increased, I need them*/

7     0     10    /*Account balance increased, I need them*/

8    -500   10    /*Account balance increased, I need them*/

9    -500   -10  /*Account balance increased but I do not need this kind of customers 'cos they have negative balances in both periods*/

10   10     0      /*Redeemed, I need them*/

11  10     -50   /*Redeemed, I need them*/

;

RUN;

PPS_BAL6 stands for bank account balance 6 months prior today (t_6).

PPS_BAL0 stands for bank account balance as of today (t_0).

I need to categorize the customers into following groups:

1. redeemed 

2. increased

3. decreased

4. unchanged

I have attempted the below but it does not capture the categories properly.

DATA TT;

SET T;

if PPS_BAL6 >0;

if (PPS_BAL6 - PPS_BAL0 = PPS_BAL6) then category = 'redeemed';

else if  (PPS_BAL6 - PPS_BAL0 > 0) then category = 'decreased';

else if  (PPS_BAL6 - PPS_BAL0 < 0) then category = 'increased';

else if  (PPS_BAL6 - PPS_BAL0 = 0) then category = 'unchanged';

run;

I would really appreciate any help.

Thanks

Mirisage

[Linlin · Accepted solution]

data have;

input customer_ID PPS_BAL6 PPS_BAL0;

datalines;

1    10      8      

2    -500  -1000

3    10     10   

4     0      0     

5   -500   -500  

6    10    12   

7     0     10 

8    -500   10 

9    -500   -10

10   10     0  

11   10     -50

;

RUN;

data want;

  length category $ 10;

  set have;

  if  pps_bal6 >pps_bal0>0 then do;

     category='decreased'; output;end;

  if max(pps_bal6,pps_bal0)<0 then delete;

  if (pps_bal6=pps_bal0) and pps_bal0>0 then do;

     category='unchanged';output;end;

  if (pps_bal6=pps_bal0) and pps_bal0=0 then delete;

  if  pps_bal6<pps_bal0 and pps_bal0>0 then do;

     category='increased';output;end;

  if  pps_bal6>pps_bal0 and pps_bal6>0 and pps_bal0 le 0 then do;

     category='redeemed';output;end;

run;

proc print data=&syslast;

title from &syslast;run;

                      Obs    category         ID       PPS_BAL6    PPS_BAL0

                       1     decreased         1            10           8

                       2     unchanged         3            10          10

                       3     increased          6            10          12

                       4     increased          7              0          10

                       5     increased          8         -500          10

                       6     redeemed         10           10           0

                       7     redeemed         11           10         -50

Linlin

[Ksharp]

How about:

DATA TT;

SET T;

if PPS_BAL6 >0 then do;

if (PPS_BAL6 - PPS_BAL0 = PPS_BAL6) then category = 'redeemed';

else if  (PPS_BAL6 - PPS_BAL0 > 0) then category = 'decreased';

else if  (PPS_BAL6 - PPS_BAL0 < 0) then category = 'increased';

else if  (PPS_BAL6 - PPS_BAL0 = 0) then category = 'unchanged';

end;

run;

Ksharp

[Mirisage]

Hi Ksharp,

This a nice shortcut way. Although it doesn't generate intended results, I am sure this code can be tweaked to get the correct answer.

Warm regards

Mirisage

[Mirisage]

Hi Linlin,

This is excellent!

Exactly correct and I reallly appreciate it.

Mirisage