BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
EK-BALAM
Calcite | Level 5

Mini – Project of Advanced SAS Course

 

Part One: Small Project for the Telecom company

 

Customer Distribution and Deactivation Analyses

Objective:

The attached data is the CRM data of a wireless company for 2 years. The wireless company would like to investigate the customer distribution and business behaviors, and then gain insightful understanding about the customers, and to forecast the deactivation

trends for the next 6 months.

Data:

Acctno: account number.

Actdt: account activation date

Deactdt: account deactivation date

DeactReason: reason for deactivation.

GoodCredit: customer’s credit is good or not.

RatePlan: rate plan for the customer.

DealerType: dealer type.

Age: customer age.

Province: province.

Sales: the amount of sales to a customer.

 

 

Analysis requests:

1.1  Explore and describe the dataset briefly. For example, is the acctno unique? What

is the number of accounts activated and deactivated? When is the earliest and

latest activation/deactivation dates available? And so on….

 

1.2  What is the age and province distributions of active and deactivated customers?

 

 

*1.3 Segment the customers based on age, province and sales amount:            ;
************************************************************************************;
PROC FORMAT;
VALUE AG
    0-<20='YOUNG'
	20-<40='ADULT'
	40-<60='MATURE'
	60-high='Senior'	
;
VALUE sal
   0-<100='FAIR'
   100-500='NORMAL'
   501-800='GOOD'
   801-high='EXCELENT'
 ;
run;

Sales segment: < $100, $100---500, $500-$800, $800 and above.

Age segments: < 20, 21-40, 41-60, 60 and above.

Create an analysis report by using the attached Excel template.

 

1.4.Statistical Analysis:

1) Calculate the tenure in days for each account and give simple statistics.

2) Calculate the number of accounts deactivated for each month.

3) Segment the account, first by account status “Active” and “Deactivated”, then by

Tenure: < 30 days, 31---60 days, 61 days--- one year, over one year. Report the number of accounts of percent of all for each segment.

4) Test the general association between the tenure segments and “Good Credit”

“RatePlan ” and “DealerType.”

5) Is there any association between the account status and the tenure segments?

Could you find out a better tenure segmentation strategy that is more associated

with the account status?

6) Does Sales amount differ among different account statuses, good credit, and

customer age segments?

hello i would really appreciate help with the code fore the questions in blue, ive been trying for days and cant figure it out, thanks for your time 

EKBALAM_0-1648165830479.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
tarheel13
Rhodochrosite | Level 12

ok I think you can do if nmiss(deactdt, actdt) = 0 then tenure = deactdt-actdt. then use the ranges they gave you to calculate tenure. you can do it in proc format. 

 

< 30 days, 31---60 days, 61 days--- one year, over one year. Report the number of accounts of percent of all for each segment.

proc format;
value tenfmt
0-<31='< 30 days'
31-<61='31-60 days'
61-<366='61 days - one year'
366-HIGH='Over 1 year'
;
run;

then in a data step do tenure_seg =put(tenure, tenfmt.);

View solution in original post

12 REPLIES 12
tarheel13
Rhodochrosite | Level 12

was there a question here? 

EK-BALAM
Calcite | Level 5
I would like help with the answers to the questions highlighted
V168
Calcite | Level 5

Hi there: I think I have the same project and the same data file as you, I have a problem to read the source file into sas like you post the file. can you send me the code to read the file into sas.     Thanks

tarheel13
Rhodochrosite | Level 12

how are you defining active and deactivated? it looks like you will have to do if deactdt =. then status='Active'; else status='Deactivated';

EK-BALAM
Calcite | Level 5
what's the question for this answer?

thanks for your time with the questions in blue

3) Segment the account, first by account status “Active” and “Deactivated”, then by

Tenure: < 30 days, 31---60 days, 61 days--- one year, over one year. Report the number of accounts of percent of all for each segment.

4) Test the general association between the tenure segments and “Good Credit”

“RatePlan ” and “DealerType.”

5) Is there any association between the account status and the tenure segments?

Could you find out a better tenure segmentation strategy that is more associated

with the account status?

6) Does Sales amount differ among different account statuses, good credit, and

customer age segments?

hello i would really appreciate help with the code fore the questions in blue, ive been trying for days and cant figure it out, thanks for your time
tarheel13
Rhodochrosite | Level 12

how do you get tenure? 

EK-BALAM
Calcite | Level 5
I don't know, I assume from deactivation date, but i dont know how to do the code for segmentation
tarheel13
Rhodochrosite | Level 12

ok I think you can do if nmiss(deactdt, actdt) = 0 then tenure = deactdt-actdt. then use the ranges they gave you to calculate tenure. you can do it in proc format. 

 

< 30 days, 31---60 days, 61 days--- one year, over one year. Report the number of accounts of percent of all for each segment.

proc format;
value tenfmt
0-<31='< 30 days'
31-<61='31-60 days'
61-<366='61 days - one year'
366-HIGH='Over 1 year'
;
run;

then in a data step do tenure_seg =put(tenure, tenfmt.);

EK-BALAM
Calcite | Level 5
thanks allot for your time, much appreciated
EK-BALAM
Calcite | Level 5

hello you have any idea how to do this 

 

4) Test the general association between the tenure segments and “Good Credit”

“RatePlan ” and “DealerType.”

5) Is there any association between the account status and the tenure segments?

Could you find out a better tenure segmentation strategy that is more associated

with the account status?

6) Does Sales amount differ among different account statuses, good credit, and

customer age segments?

 

from the same dataset 

tarheel13
Rhodochrosite | Level 12

not too familiar with customer segmentation but maybe proc glm if the assumptions are met.

V168
Calcite | Level 5

Hi there:

I think I have the same project as you and the same data set, I have a problem reading the sata set into the sas table, can you send me the code to read the source data set to sas table. Thanks!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 3589 views
  • 1 like
  • 3 in conversation