I'm trying to assess what percent of callers' issues are resolved within the caller's first call. Currently, a repeat call is defined as a call placed within 14 days of a customer's first call (i.e. callback window = 14 days). I'm trying to determine if this 14-day callback window can be justifiably shortened (i.e. determine what a proper cutoff value for a callback is).   I'm thinking of collecting a sample of calls and running logistic regression to model a repeat call having the same call intent as the first call as a function of time. (The thinking is that as more time passes after the first call, the less likely the caller's subsequent call is related to the first call).   Would it be valid to specify 15 logistic models in which I first define callback window as 0 days and increment by 1 day up to 14? So callback window would be defined as 0 days in first model, 0-1 days in second, 0-2 days in third, and so on. Is it a valid approach to choose the model with the highest area under the curve to determine the cut-off value for my callback window? ... View more

I've created a macro that creates a macro variable containing the names of tables with 0 observations. Within this macro, I also send an email if any tables are empty/have 0 observations. For some reason, the value of the macro variable containing name of empty tables isn't being included in my email. The email is sent, but the macro var's value isn't included in the email. What am I doing wrong?   Reprex with notes/comments below: /*include names of tables to check*/ %let tables = a b c; /*the corresponding row counts for the tables in &tables*/ %let table_row_counts = 0 100 500; %let number_of_tables = %sysfunc(countw(&tables)); /*Check each table's row counts, if any table has 0 rows, then include table name in &empty_tables*/ %macro validate(); /*Test macro -- this is sucessfully included in email*/ %let test = test message; %let empty_tables = ; %do i = 1 %to &number_of_tables; %if %scan(&table_row_counts,&i) = 0 %then %do; %let empty_tables = %scan(&tables,&i) &empty_tables; %end; %end; %let total_empty_tables = %sysfunc(countw(&tables)); %put &empty_tables; /*Resolves to "a" here, but is missing in email sent below*/ %if &total_empty_tables > 0 %then %do; * Email saying module has failed; FILENAME Mailbox EMAIL ('your_email@email.com') Subject='Empty Table Alert'; DATA _NULL_; FILE Mailbox; put "&test"; PUT "The following tables are empty"; put &empty_tables; RUN; %return; %end; %mend; %validate(); ... View more

I've created a macro to check the observation counts of tables and include the names of any empty tables inside a macro variable. Inside of the macro, I'm trying to email myself a message detailing which, if any, tables are empty. However, the value of my macro variable does not get included in my email. What am I doing wrong? I've included a reprex below: /*list of table names*/ %let tables = a b c; /*row counts corresponding to table names in &tables*/ %let table_row_counts = 0 100 500; %let number_of_tables = %sysfunc(countw(&modules)); /*check to see if tables are empty, if any are, then include email with the names of the empty tables*/ %macro validate(); /*test macro variable. this macro var is properly included in email*/ %let test = test message; %let empty_tables = ; %do i = 1 %to &number_of_tables; %if %scan(&table_row_counts,&i) = 0 %then %do; %let empty_tables = %scan(&tables,&i) &empty_tables; %end; %end; %let total_empty_tables = %sysfunc(countw(&tables)); %put &empty_tables; /*resolves to "a"*/ /*email will display the value of &test, but not &empty_tables (value should be displayed as "a"*/ %if &total_empty_tables > 0 %then %do; FILENAME Mailbox EMAIL ('youremail@email.com') Subject='Empty Table Alert'; DATA _NULL_; FILE Mailbox; put "&test"; PUT "The following tables are empty"; put &empty_tables; RUN; %return; %end; %mend; %validate(); ... View more

I'm summing a field within a join subquery to calculate a field's total and join it to a given table. However, I'm having difficulty handling null values.   I realize a null value plus anything will equal null, so I hoped to handle with coalesce within my sum function. This approach is not working for me, because not all tables contain all IDs. When such a table is joined, I end up with a null value. I'd like to handle these instances conditionally, so that a null total is equal to an employee's max total if this value is non-null or 0 if this value is null.    I've included a reprex below. What adjustments should I make to end up with non-null totals for all records in my joined table? Thank you.   /*sample table a*/ data table_a; input id title $ region $ calls; cards; 1 manager south 30 1 agent north 20 2 manager west 20 2 agent south 25 ; run; /*sample table b*/ data table_b; input id title $ sales; cards; 1 manager 20 2 manager 5 2 agent 3 ; run; /*sample table c*/ data table_c; input id title $ leads; cards; 1 manager . 1 agent 10 ; run; /*join tables (in this code, my sums return null values for my calculated totals for IDs where records don't exist in all tables. e.g. total_sales are null for id = 1, title = agent total_leads are null for id 2 I'd like to replace null values with an employee's max total (20 for employee 1's total sales) or 0 if max total is null (0 for employee 2's total leads)*/ proc sql; create table reprex as select a.id, a.region, a.calls, a.title, b.total_sales, b.sales, c.total_leads, c.leads from table_a as a left join (select sum(coalesce(sales, 0)) as total_sales, sales, id, title from table_b group by id) b on a.id = b.id and a.title = b.title left join (select sum(coalesce(leads, 0)) as total_leads, leads, id, title from table_c group by id) c on a.id = c.id and a.title = c.title; quit;   ... View more