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

[anilgvdbm]

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?

[Ksharp]

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

[anilgvdbm]

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

[Ksharp]

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

Why is there some missing value for mobile_num ?

[anilgvdbm]

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.

[PeterClemmensen]

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?

[anilgvdbm]

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.

[Ksharp]

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;

[anilgvdbm]

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.

[Ksharp]

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 .

[PeterClemmensen]

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

[PeterClemmensen]

"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? 🙂