DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 68

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

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?

Super User
Posts: 10,784

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

Posted in reply to anilgvdbm

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

Contributor
Posts: 68

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

[ Edited ]

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
Super User
Posts: 10,784

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

Posted in reply to anilgvdbm

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

Why is there some missing value for mobile_num ?

Contributor
Posts: 68

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

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.
PROC Star
Posts: 1,283

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

Posted in reply to anilgvdbm

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?

Contributor
Posts: 68

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

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.

Super User
Posts: 10,784

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

Posted in reply to anilgvdbm

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;

Contributor
Posts: 68

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

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.

Super User
Posts: 10,784

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

[ Edited ]
Posted in reply to anilgvdbm

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 .

PROC Star
Posts: 1,283

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

Posted in reply to anilgvdbm

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

PROC Star
Posts: 1,283

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

Posted in reply to anilgvdbm

"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? Smiley Happy

Ask a Question
Discussion stats
  • 11 replies
  • 164 views
  • 0 likes
  • 3 in conversation