BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

View solution in original post

4 REPLIES 4
Linlin
Lapis Lazuli | Level 10

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
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7

Hi Linlin,

This is excellent!

Exactly correct and I reallly appreciate it.

Mirisage

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 781 views
  • 0 likes
  • 3 in conversation