Many thanks! ... View more

Thank you for your help! ... View more

Thanks a lot! ... View more

Thank you so much! ... View more

Hi ballardw, my purpose is calculate churn rate using CNT_terminate divided  by CNT_active if these two variables are in a same dataset. Like this: customer_id      start_date            cancel_date        CNT_terminate     CNT_active 8661                29APR2002          30APR2004             154                        3243 8663                10May2003          30APR2005              142                       4904 ... View more

Hi, here's the description of my question. Thanks in advance everyone!!!       sd.Tel_data is a whole dataset including information of both active and cancelled customers.Like this:   customer_id      start_date            cancel_date 8661                   29APR2002          30APR2004 8662                   20APR2000                            . 8663                   10May2003          30APR2005       sd.active is subsetted from sd.Tel_data, which only contains active customers' information. Like this: customer_id      start_date            cancel_date 8662                   20APR2000                            .     The code blow is to figure out number of active customers each month:   data  sd.active; set sd.Tel_data  ; if cancel_date EQ missing;  run; %macro active_account(input, output); data &output; set sd.active; if start_date LE  &input; run;  %mend; %active_account('30APR2004'D, sd.active_APR2004); %active_account('30APR2005'D, sd.active_APR2005); %active_account('30APR2006'D, sd.active_APR2006); %active_account('30AUG2004'D, sd.active_AUG2004); %active_account('30AUG2005'D, sd.active_AUG2005); %active_account('30AUG2006'D, sd.active_AUG2006);          The dataset below called sd.Temo which contains information of cancelled customers. This dataset has variables like customer_id, start_date, cancel_date. (sd.Temo is also subsetted from whole dataset sd.Tel_data)     customer_id      start_date            cancel_date        CNT_terminate 8661                   29APR2002          30APR2004             154 8663                   10May2003          30APR2005              142     What I need to do is to derive a new variable called CNT_active which means number of active customers each month in dataset  sd.Temo, and populate the results of number of active customers each month I got from Macro above to new column CNT_active. My purpose is calculate churn rate using CNT_terminate divided  by CNT_active if these two variables are in a same dataset. Like this: customer_id      start_date            cancel_date        CNT_terminate     CNT_active 8661                29APR2002          30APR2004             154                        3243 8663                10May2003          30APR2005              142                       4904     sd.active and sd.Temo don't have any common key variables such as customer_id because one table is for active customers and the other is for cancelled customers, they are not overlapping.  But how to derive the new variable CNT_active  in dataset sd.Temo and populate the results from Macro which are 3243, 4904, 4904,3723, 4904, 4904 into variable CNT_active based on same month and year?   Here's the results from Macro above:   ... View more

Thank you so much for you reply!  sd.Tel_data is a whole dataset including both active and cancelled customers.Like this:   customer_id      start_date            cancel_date 8661                   29APR2002          18APR2005 8662                   20APR2000                            . 8663                   10May2003          18OCT2006       sd.active only contains active customers' information. Like this: customer_id      start_date            cancel_date 8662                   20APR2000                            .     The code blow is to figure out number of active customers each month:   data  sd.active; set sd.Tel_data  ; if cancel_date EQ missing;  run; %macro active_account(input,output); data &output; set sd.active; if start_date LE  &input; run;  %mend; %active_account('30APR2004'D, sd.active_APR2004); %active_account('30APR2005'D, sd.active_APR2005); %active_account('30APR2006'D, sd.active_APR2006); %active_account('30AUG2004'D, sd.active_AUG2004); %active_account('30AUG2005'D, sd.active_AUG2005); %active_account('30AUG2006'D, sd.active_AUG2006);   The dataset below called sd.Temo which contains information of cancelled customers. This dataset has variables like customer_id, start_date, cancel_date. (sd.Temo is also subsetted from whole dataset sd.Tel_data)   customer_id      start_date            cancel_date        CNT_terminate 8661                   29APR2002          18APR2005             154 8663                   10May2003          18OCT2006             142     What I need to do is to derive a new variable called "CNT_active " which means number of active customers each month in dataset  sd.Temo, and populate the results of active customers each month I got from Macro above to new column CNT_active. Like this: customer_id      start_date            cancel_date        CNT_terminate     CNT_active 8661                   29APR2002          18APR2005             154                       3733 8663                   10May2003          18OCT2006             142                       4890   sd.active and sd.Temo don't have any common key variables such as customer_id because one table is for active customers and the other is for cancelled customers, they are not overlapping.  Could you please give me some advice? Thanks a lot!!     ... View more

HI Jag, I use Macro to get six datasets and their number of records are 3243, 4904, 4904, 3723, 4904,4904. What I need to do is populate these values to a new variable called "new_column" based on same month and year. After that, I will derive a new variable by using variable CNT_terminate and new_column. But the thing is how to fill those values 3243, 4904, 4904, 3723, 4904,4904 into the variable new_column? Thanks a lot!   ... View more