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? 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.