topic Re: need to delete customer who is not recieving call for last 12 months in SAS Programming

Re: need to delete customer who is not recieving call for last 12 months

anilgvdbm — Mon, 11 Dec 2017 12:08:03 GMT

Dear All,

let's say i have data for12 months. variables in the data Mobile number, month and promotion audio heard and other variables. I want to remove the customers who are not at all receiving the call at least once in last 12 months and also who received but audio heard less than 50% also i have to remove.

is there any way to write loops or any suggestions?

Re: need to delete customer who is not recieving call for last 12 months

Ksharp — Mon, 11 Dec 2017 12:13:49 GMT

Better to post the dataset you have and the output you want see.

Re: need to delete customer who is not recieving call for last 12 months

PeterClemmensen — Mon, 11 Dec 2017 12:17:42 GMT

Post some sample of your data and at least a basic description of what you want your output data to look like 🙂

Re: need to delete customer who is not recieving call for last 12 months

PeterClemmensen — Mon, 11 Dec 2017 12:21:09 GMT

"I want to remove the customers who are not at all receiving the call at least once in last 12 months".

What variable indicates incomming calls? 🙂

Re: need to delete customer who is not recieving call for last 12 months

anilgvdbm — Mon, 11 Dec 2017 12:42:05 GMT

I have a dataset with the mobile number, the bucket of Audio heard, month. I have to exclude the customers who are having blank,"-" and less than 50% audio heard from last 12 months. if customer listens once in last 12 months then he should not exclude.

SAS Output

Obs	mobile_num	Disposition	Bucket_of_Audio_Heard	Month
1		Success	Less Than 50%	4
2		Success	95% - 99%	8
3		Success	95% - 99%	1
4		Ring Timeout	-	7
5		Success	Less Than 50%	6
6		Ring Timeout	-	3
7		Other error	-	5
8			-	10
9		Success	75% - 95%	9
10	-1234567	-	-	11
11	07223202201	-	-	12
12	07223235566	-	-	12
13	1032068450	Invalid Number	-	12
14	1032068450	Hung Up	-	12
15	1032068450	Ring Timeout	-	12
16	1032068450	Hung Up	-	12
17	1049061654	Invalid Number	-	12
18	1049061654	Hung Up	-	12
19	1049061654	Hung Up	-	12
20	12345678	Invalid	-	11

Re: need to delete customer who is not recieving call for last 12 months

Ksharp — Mon, 11 Dec 2017 12:45:27 GMT

This is the table you have or the output you want to see ?

Why is there some missing value for mobile_num ?

Re: need to delete customer who is not recieving call for last 12 months

anilgvdbm — Mon, 11 Dec 2017 12:48:26 GMT

only 9 mobile numbers missing this is the raw data table.. i want output like i need to exclude mobile number who are not listened at least once in 12 months.

Re: need to delete customer who is not recieving call for last 12 months

PeterClemmensen — Mon, 11 Dec 2017 12:56:54 GMT

A few things..

"I have to exclude the customers who are having blank,"-" and less than 50% audio heard from last 12 months"

- All the observations with non blank mobile_num have blank("-") Bucket_of_Audio_Heard ?

- What does the month column represent? Does 12 mean december? And what december is that?

Re: need to delete customer who is not recieving call for last 12 months

Ksharp — Mon, 11 Dec 2017 13:05:51 GMT

Sorry. I am still confused. Hope the following could give you a start.

proc sql;

create table want as

select *

from have

group by mobile_num

having sum(Disposition='Success')=0 ;

quit;

Re: need to delete customer who is not recieving call for last 12 months

anilgvdbm — Mon, 11 Dec 2017 13:26:16 GMT

Ok ,'ll clarify the issue here. in the Bucket of Audio heard variable there are 3 buckets (" ". "-". "less than 50%") so who is falling into the 3 buckets for all the 12 months then he should remove.
let's say we send a voice message to the same customer 12 times if he listens at one time then he will come under heard category if he does not listen at least one he should exclude.

Re: need to delete customer who is not recieving call for last 12 months

anilgvdbm — Mon, 11 Dec 2017 13:28:50 GMT

in month variable 12 means December. I want to exclude customer who is coming under the bucket of audio heard (blanks, "-", less than 50%) for all 12 months then he should exclude.

Re: need to delete customer who is not recieving call for last 12 months

Ksharp — Mon, 11 Dec 2017 13:45:21 GMT

OK . But you should post some more data which contains all the 12 month.

proc sql;

create table REMOVE as

select *

from have

group by mobile_num

having sum( Bucket_of_Audio_Heard in (" " "-" "less than 50%") ) >=12 ;

quit;

P.S. change something - remove dot in it and change variable name .