Find Same Day in Alternating Weeks

[Jeff_DOC]

Good afternoon.

I am looking for a way to find the same Monday each week but identify those Mondays on every other week. For instance using the code below, if January 3, 2022 is a RUN_REPORT = 'YES', then January 10, 2022 should be a RUN_REPORT = 'NO' and January 17, 2022 should be back to RUN_REPORT = 'YES'.

I attempted to use the MOD function but I'm not familiar with it enough to know how to use it correctly.

What I'm apparently doing is to set the RUN_REPORT to 'YES' for two complete weeks and then to 'NO' for two more weeks.

Temp_date will be reassigned as the run date (today()) when testing is completed so the January dates in the example are just for test purposes to attempt to locate where I'm going wrong.

I'd really appreciate any help someone could give me.

Thank you.

%let temp_date = '03jan2022'd;
/*%let temp_date = '10jan2022'd;*/
/*%let temp_date = '17jan2022'd;*/

data params;
	format week_number_start week_number_end mmddyy10.;

		week_number = week(&temp_date.);
		week_number_start = intnx('week',&temp_date.,-2,'beg');
		week_number_end = intnx('week',&temp_date.,-1,'end') +1;
	if mod(week(&temp_date.),3) > 0 then do;
		run_report = 'YES';
	end;
	else do;
		run_report = 'NO';
	end;
	test = mod(week(&temp_date.),3);
run;

[ballardw]

I am not at all sure what the output for this exercise is supposed to look like. I think you are seriously reducing the information that you have and pulling out one bit without sharing things like where does a date come from? Where is the original "run_report" value to be found.

BTW, you complicate things a lot by using 'Yes' and 'No' for actual values. If you use 1/0 numeric SAS will treat 1 as "true" or "yes" in logical expressions and 0 as "false" or "no".

You might think about using the 'S' parameter for INTNX. When you do that you can adjust a date to the same day of the week.

data test;
   x='03jan2022'd;
   /* 1 week before*/
   y = intnx('week',x,-1,'S');
   /* 1 week later*/
   z = intnx('week',x,1,'S');
   format x y z date9.;
run;

This may give a clue to what you may actually need by creating a sequence of dates based on a given date and the status of the "report", and setting a Run_report as a 1/0 coded variable.

data example;
   x='03jan2022'd;
   /* 1 means the date in x had the report,0 not*/ 
   startreport=1;
   do weeks=1 to 52;
      z = intnx('week',x,weeks,'S');      
      if startreport=1 then run_report= mod(weeks-1,2);
      else  run_report= mod(weeks,2)
      output;
   end;
   format x z date9.;
run;
      

MOD basically gives you the remainder . If the second parameter divides "evenly" into the first then you get 0, meaning no remainder. Mod(5,2) = 1  as in 2 goes into 5 2 times with 1 remainder.

If you really need to see 'Yes'/'No' it is pretty trivial to create a custom format with Proc Format to display 1 as 'Yes' and 0 as 'No'.

[Astounding]

It's not clear what pieces of this you really need, so to illustrate let me throw out a program that runs once per month.

%let temp_date = '03jan2022'd;

data params;
  format Monday week_number_start week_number_end mmddyy10.;
  week_number = 1;
  Monday = &temp_date;
  run_report='YES';
  week_number_start = Monday - 1;
  week_number_end = Monday + 5;
  output;
  week_number = 2;
  run_report='NO';
  Monday + 7;
  week_number_start + 7;
  week_number_end + 7;
  output;
  week_number = 3;
  run_report='YES';
  Monday + 7;
  week_number_start + 7;
  week_number_end + 7;
  output;
  week_number = 4;
  Monday + 7;
  week_number_start + 7;
  week_number_end + 7;
  output;
run;

It spells out things a little redundantly, but that's important to verify that you understand how the date variables are working.  How close does this come to pointing you in a good direction?

On a related note, will the %let statements always identify a Monday?  Should the program verify that fact?

[Jeff_DOC]

Good morning.

Thanks to both of you for taking the time to respond. I probably did not explain some of this very well so here I go again. 

I don't think it matters what the data (have) looks like since I'm only looking to set a YES/NO switch based on what day it is today (or the run date). The code that executes based on the YES/NO will execute just fine if the switch is set to YES (then execute the code). If today is not Monday in two-week intervals then do not execute the code. I'm not looking at the data to find records where a date field matches a particular date. I'm looking to conditionally execute code based on the params logic all using the week number of the year using run_date.

What I was hoping to do, using the mod function, was to determine if the return value was a whole number and if so execute the code. If there was a residual (anything after the decimal) then do not execute. So, given the week number of the year, for instance week 6 divided by 2 would leave me with 3-a whole number so the code should execute. However, week 7 divided by 2 would leave me with 3.5-not a whole number and so the code should not execute.

This was the starting point and there are probably way better ways to do it but I thought this might be an easy way to try to do it.

[Astounding]

You are still missing an important piece.  Is the value supplied by the %LET statement always a Monday?  And will that value always be earlier than the date the program runs?

Seriously, the code might be as simple as:

data _null_;

if today() in (&temp_date, &tempdate+14, &tempdate+28) then run_report='YES';

run;

Why would it not be that simple?

[Jeff_DOC]

Hi Astounding.

Thanks very much for taking the time to reply.

Yes, the value supplied by the %LET will always be a Monday. I Temp_Date will always be the run date and will change to the current date each time it runs. Since temp_date isn't static, and even if it was, I can't check for plus 7 or plus 14 since on the fourth run it would be plus 28. I know it could probably be better structured but this is what I got. Do you think there's a simple way to check to see if the week number of the year, based on the run date, is even or odd?

[PaigeMiller]

---

@Jeff_DOC wrote:

Temp_Date will always be the run date and will change to the current date each time it runs. Since temp_date isn't static, and even if it was, I can't check for plus 7 or plus 14 since on the fourth run it would be plus 28.

---

But you can check to see how many days the temp_date is since the Monday specified in %LET. If the temp_date is Wednesday (two days after that Monday) then subtract 2 and then its easy to begin your counting from the previous Monday. Then you can check to see what day is 7 days after that (which you don't want) and what day is 14 days after that (which you do want) and what day is 21 days after that (which you do want) and what days is 28 days after that (which you do want) and so on, this is simply taking the number of days and then using the MOD function to see if the remainder is 0 when you do the arithmetic mod 14.

[Tom]

This is still as clear as mud.

You need TWO dates.

You need any previous date that the report should have run.

You need the date you are testing to see if the report should run.  This could be TODAY if you want.

Then you just need to test if the DIFFERENCE is a multiple of 14 days.

Let's assume the report is supposed to run on January 3,2022 and then on January 17, 2022, etc.

So set your base date to 03JAN2022.

%let basedate='03JAN2022'd;

Now to test if today should be a run just do:

%if %sysfunc(mod(%sysfunc(today())-&basedate,14)) %then %do;
  %let run=NO;
%end;
%else %do;
  %let run=YES;
%end;

To test some other date use that date in place of %SYSFUNC(TODAY()).

[mkeintz]

What are you starting out with?  I.e., what does the data look like before you generate run_report values.

Let's say you are starting with a data set HAVE containing a series of dates in variable DATE, and you want to establish

1. run_report="YES" for 03jan2022 and all dates with the same day-of-week and an even number of weeks afterward.
2. run_report='NO' for all dates having the same day-of-week and an odd number of weeks afterward, 

Then:something like this would work.

%let first_yes_date=03jan2022;

data want;
  set have;
  if mod(date-"&first_yes_date"d,14)=0 then run_report="YES";
  else if mod(date-"&first_yes_date"d,7)=0 then run_report="NO";
run;

So the MOD function does what is needed by utilizing the property that SAS date values are numeric values of day counts from 01jan1960.   This means you can subtract one date from another to get number of days passed.  The program above sets "YES" for every date that is a multiple of 14 days away from 03jan2022.   And sets "NO" for every date that is a multiple of 7 days away from 03jan2022 - except for the "YES" dates.   All other dates, if any, will have a blank value for run_report.  

This works fine for intervals that are regular - i.e. calendar days as the metric.  But a mod function would not work if you were using irregular terms like MONTHS as the interval.  Then you would have to gain some mastery over various SAS date functions, or date-intervals functions. 

[Jeff_DOC]

Thank you to all who responded to help.

After looking over all the responses, I decided to go with mod(week(&temp_date.),2) = 0. Since the code will only actually run on Monday anyhow  checking to see if the current week is even or odd should allow it to run only on even weeks of the year. 

Again, thank you to all and as always, I learned a lot.

[Tom]

Note that the WEEK() function can return a 53 so your algorithm might break down.

But if you only run on Mondays you are safe until 2040.

1891  data check;
1892  do date='03JAN2022'd to '01jan2099'd by 7;
1893    week=week(date);
1894    if week>52 then output;
1895  end;
1896  format date yymmdd10.;
1897  run;

Obs          date    week

 1     2040-12-31     53
 2     2068-12-31     53
 3     2096-12-31     53

[Jeff_DOC]

Thanks for the heads up. I'll try to remember that for the next 18 years 🙂

Seriously though, you are correct in that the code should be written correctly in order to take that into account. I'll have to think about that.

Thank you Tom.

[Tom]

It can also return WEEK=0.  So the issue is more pressing as you will start seeing issue in 2024.

Obs          date    week

  1    2024-01-01      0
  2    2029-01-01      0
  3    2035-01-01      0
  4    2040-12-31     53
  5    2046-01-01      0
  6    2052-01-01      0
  7    2057-01-01      0
  8    2063-01-01      0
  9    2068-12-31     53
 10    2074-01-01      0
 11    2080-01-01      0
 12    2085-01-01      0
 13    2091-01-01      0
 14    2096-12-31     53

[Patrick]

@Jeff_DOC 

If you just want to run in two week intervals on Mondays then what @Tom and @mkeintz proposed should both work. You just need to select the date of your very first Monday run.