topic Recoding a variable? in SAS Procedures

Recoding a variable?

Mirisage — Fri, 16 Dec 2011 00:02:07 GMT

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

Re: Recoding a variable?

Linlin — Fri, 16 Dec 2011 01:00:54 GMT

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

Recoding a variable?

Ksharp — Fri, 16 Dec 2011 03:37:30 GMT

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

Recoding a variable?

Mirisage — Fri, 16 Dec 2011 13:59:05 GMT

Hi Linlin,

This is excellent!

Exactly correct and I reallly appreciate it.

Mirisage

Recoding a variable?

Mirisage — Fri, 16 Dec 2011 14:02:19 GMT

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