How to alternate between two variables based on the day of the week - SAS 9.4

[robulon]

Hi all,

This is one of those things that could be done manually but I've got it in my head that I want to do it in SAS. I've been tasked with creating a data table identifying which customer (1 or 2) to dial out to on any given day. The current system switches customer automatically every 3 days with the first day customer 2 was dialled being 31st March 2018. I want to understand the implications if the number of days between switchovers was changed and want to be able to produce a code where the only thing I would need to change would be the number of days between switchovers. There are a couple of complications as a file isn't created on a Monday which means if the natural switchover date falls on a Monday, the same customer would continue to be dialled for the next 3 days. The code I have which seems to work for most variations of days is : -

data experiment_2a;

set all_dates;

format word_day weekdate.;

if date = '31mar2018'd then dial = "Cust_2";

day = mod(juldate(date),1000);

word_day = date;

if weekday(date) = 1 then dial_day = 0;

else dial_day = 1;

if weekday(date) ne 2 then do;

if mod(day,3) = 0 then switchover = 1;

end;

if switchover = 1 and lag(dial) = "Cust_2" then dial = "Cust_1";

else if switchover = 1 and lag(dial) = "Cust_1" then dial = "Cust_2";

retain dial;

run;

The set all_dates literally just contains a list of dates from 31st March 2018 - 31st December 2018 and all the other variables are created in the above step.

I've also attached a Word document screenshots of the outputs comparing the results from a three day cycle (which looks correct) and a one day cycle (which doesn't).

As I say, this seems to work for most of the variations of days I put in using the mod function but stops working when try to alternate every day (so create a switchover value when mod(day,1) = 0. Also, I have read that I shouldn't be using the lag function conditionally so if anyone has any suggestions for a better way of doing this, I would be delighted to learn about it.

Thanks in advance,

Rob

[PaigeMiller]

Some of us won't download or open Microsoft Office documents as they are a security risk. Can you include the results directly in your message?

With regards to the LAG function, you use it in an unconditional manner to create a new variable, then use the new variable in your IF statement. Example:

prev_dial=lag(dial);
if switchover = 1 and prev_dial = "Cust_2" then dial = "Cust _1";

[robulon]

Thanks Paige, and apologies about the document.

The problem with what you have suggested is that the only time the dial variable is created unconditionally is when cust_2 is assigned where the date is 31st March 2018. All the other values of dial are created conditionally based on the result of the lag. The retain statement keeps the previous value of dial in where switchover does not equal 1.

Tried to include the results directly below, this is the three day switch

   Obs          DATE            word_day             dial     day    dial_day    switchover    
                                                                                                 
       1    31/03/2018    Saturday, March 31, 2018    Cust_2     90        1            1        
       2    01/04/2018       Sunday, April 1, 2018    Cust_2     91        0            .        
       3    02/04/2018       Monday, April 2, 2018    Cust_2     92        1            .        
       4    03/04/2018      Tuesday, April 3, 2018    Cust_1     93        1            1        
       5    04/04/2018    Wednesday, April 4, 2018    Cust_1     94        1            .        
       6    05/04/2018     Thursday, April 5, 2018    Cust_1     95        1            .        
       7    06/04/2018       Friday, April 6, 2018    Cust_2     96        1            1        
       8    07/04/2018     Saturday, April 7, 2018    Cust_2     97        1            .        
       9    08/04/2018       Sunday, April 8, 2018    Cust_2     98        0            .        
      10    09/04/2018       Monday, April 9, 2018    Cust_2     99        1            .     

This is the one day switch

     Obs          DATE            word_day             dial     day    dial_day    switchover    
                                                                                                 
       1    31/03/2018    Saturday, March 31, 2018    Cust_2     90        1            1        
       2    01/04/2018       Sunday, April 1, 2018    Cust_1     91        0            1        
       3    02/04/2018       Monday, April 2, 2018    Cust_1     92        1            .        
       4    03/04/2018      Tuesday, April 3, 2018    Cust_2     93        1            1        
       5    04/04/2018    Wednesday, April 4, 2018    Cust_2     94        1            1        
       6    05/04/2018     Thursday, April 5, 2018    Cust_1     95        1            1        
       7    06/04/2018       Friday, April 6, 2018    Cust_1     96        1            1        
       8    07/04/2018     Saturday, April 7, 2018    Cust_1     97        1            1        
       9    08/04/2018       Sunday, April 8, 2018    Cust_2     98        0            1        
      10    09/04/2018       Monday, April 9, 2018    Cust_2     99        1            .      

Sorry it looks so messy,

Thanks

[Shmuel]

I'm not sure that I have understood exactly what you want  -

As the week has 7 days and you don't dial on weekday = 2 (monday)

it means that you dial on 6 days only. If you swap between 2 customers

it means that on even weekays you will dial to "cust-1" otherwise you will dial to "cust-2".

If I'm correct then you can use code like:

      if weekday in (1, 3, 5) then dial = "cust-1"; else

      if weekday in (4, 6, 7) then dial = "cust-2";

Are you sure you want to dial a customer only on specific weekdays and not try other weekdays ?

[robulon]

Sorry Shmuel, perhaps I haven't explained this very well.

We do dial on a Monday but not on a Sunday. The dial file for each day is produced by running a batch file at the end of the previous day. No dial file is produced for a Monday because there is no batch file run on a Sunday so Monday's dialling is completed using Sundays dial file which is created from Saturday's batch file (hope that makes sense!).

What you have suggested would make sense if I only wanted to alternate the days of dialling but I am trying to create a piece of code whereby I can amend the number of days between switchovers and assess the impact on contact intensity and distribution this would create. I realise that day by day alternation would mean the same customer would always be dialled on the same day which we would not want but I wanted to be able to include it for completeness and to demonstrate that I have considered all the options.

Regards,

Rob

[Shmuel]

This is not a sas technical debate but a logical design one. 

I have few more questions:

- how often do you want to dial a customer? that is from once in a period of 2 up to X max days, excluding sunday.  

- would you like to change the gap time, per customer, from minimum to maximum randomly? 

- what about assigning the customers a group code, so you select a group per run.

Maybe if you answer those questions to yourself you will come to a diffeent approach

to solve what you want.

[PaigeMiller · Accepted solution]

---

@robulon wrote:

The problem with what you have suggested is that the only time the dial variable is created unconditionally is when cust_2 is assigned where the date is 31st March 2018. 

---

Then I don't think you can have this logic in a single data step. Or you need to use a RETAIN statement rather than the LAG function.

[robulon]

Hi,

Thanks for the suggestions and help.

As per your advice, I've been able to ditch the lag function and achieve my goal just using the retain instead.

I think it was one of those things where you've got it in your head you want to do it a certain way and end up trying to shoehorn a solution in using a method that isn't really appropriate.

Cheers,

Rob