logic to find data on particular number of days

[User_2024]

Hi, I have a date dataset that assigns start date and end date based on the weekday, excluding weekend. Using  the date function I want to calculate the data on 3 days of the date window. Right now the code doesn't evaluate if the data is present on all 3 days, it just pulls whatever data is available even if its on the start date or on the end date or on both. Should introducing middle day solve the issue ? Any insights would be helpful! 

data myora.temp_dates;
  wd = weekday(today());
  if wd = 2 then end_date = today() - 3;
  else end_date = today() - 1;
  
  if wd in (2,3,4) then start_date = today() - 5;
  else start_date = today() - 3;
  
  format start_date end_date date9.;
run;

select 
       	id,
		num, 
		name,
		COUNT(*) AS name_count, 
		t.start_date,
		t.end_date,    
       
from initial_data1
cross join temp_dates t
where auth_dt >= t.start_date 
	and auth_dt <= t.end_date
	and TO_CHAR(auth_dt, 'Dy') NOT IN ('Sat', 'Sun')
group by id,num,name,t.start_date, t.end_date
having count(*) >= 10 and sum(amt) >= 100

[SASJedi]

As I understand the question, you want the End_Date to be one working day before today and Start_Date to be two working days before the End_Date, resulting in a 3-day timespan. If so, how about this?

data demo_dates;
	length Today Start_Date End_Date Weekday 8;
	do Today='29JUL2024'd to '05AUG2024'd;
  		Weekday  = weekday(today);
		end_date   = intnx('weekday', today, -1);
		start_date = intnx('weekday', end_date, -2);
		if 2<=Weekday <=6 then output;
	end;
  format today start_date end_date weekdate.;
run;

proc print data=demo_dates;
run;

Results:

Obs	Today	Start_Date	End_Date	Weekday
1	Monday, July 29, 2024	Wednesday, July 24, 2024	Friday, July 26, 2024	2
2	Tuesday, July 30, 2024	Thursday, July 25, 2024	Monday, July 29, 2024	3
3	Wednesday, July 31, 2024	Friday, July 26, 2024	Tuesday, July 30, 2024	4
4	Thursday, August 1, 2024	Monday, July 29, 2024	Wednesday, July 31, 2024	5
5	Friday, August 2, 2024	Tuesday, July 30, 2024	Thursday, August 1, 2024	6
6	Monday, August 5, 2024	Wednesday, July 31, 2024	Friday, August 2, 2024	2

[ballardw]

---

@User_2024 wrote:

Hi, I have a date dataset that assigns start date and end date based on the weekday, excluding weekend. Using  the date function I want to calculate the data on 3 days of the date window. Right now the code doesn't evaluate if the data is present on all 3 days, it just pulls whatever data is available even if its on the start date or on the end date or on both.

---

I am not sure that I understand what you want. Can you show a couple of different worked examples given a specific date as the basis and the actual dates of data that you want?

One interpretation of what I imagine you may want would be this that excludes the boundary dates.

where auth_dt > t.start_date 
	and auth_dt < t.end_date

If you are requiring data to be present on all three days that may be a bit more complicated.