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?
Better to post the dataset you have and the output you want see.
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 |
This is the table you have or the output you want to see ?
Why is there some missing value for mobile_num ?
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?
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;
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 .
Post some sample of your data and at least a basic description of what you want your output data to look like 🙂
"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? 🙂
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.