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
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
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
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
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
Hi Linlin,
This is excellent!
Exactly correct and I reallly appreciate it.
Mirisage
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.
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.