## Recoding a variable?

Solved
Super Contributor
Posts: 338

# Recoding a variable?

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

Accepted Solutions
Solution
‎12-15-2011 08:00 PM
Super Contributor
Posts: 1,636

## Re: Recoding a variable?

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

All Replies
Solution
‎12-15-2011 08:00 PM
Super Contributor
Posts: 1,636

## Re: Recoding a variable?

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

Super User
Posts: 10,784

## Recoding a variable?

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

Super Contributor
Posts: 338

## Recoding a variable?

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

Super Contributor
Posts: 338

## Recoding a variable?

Hi Linlin,

This is excellent!

Exactly correct and I reallly appreciate it.

Mirisage

🔒 This topic is solved and locked.