help with sas data set

[EK-BALAM]

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 

[tarheel13]

was there a question here? 

[EK-BALAM]

I would like help with the answers to the questions highlighted

[V168]

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]

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]

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]

how do you get tenure? 

[EK-BALAM]

I don't know, I assume from deactivation date, but i dont know how to do the code for segmentation

[tarheel13 · Accepted solution]

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]

thanks allot for your time, much appreciated

[EK-BALAM]

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]

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

[V168]

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!