BookmarkSubscribeRSS Feed
anilgvdbm
Quartz | Level 8

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?

11 REPLIES 11
Ksharp
Super User

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

anilgvdbm
Quartz | Level 8

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
Super User

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

Why is there some missing value for mobile_num ?

anilgvdbm
Quartz | Level 8
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
Tourmaline | Level 20

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
Quartz | Level 8
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
Super User

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
Quartz | Level 8
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
Super User

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
Tourmaline | Level 20

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

PeterClemmensen
Tourmaline | Level 20

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1325 views
  • 0 likes
  • 3 in conversation