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

[User_2024]

Yes., your logic for the weekday is correct, There is no issue in the date function, the problem is I want to pull the accts if they have data on ALL 3 days not just on 1 day or 2 days. 

I made an example data table, my logic is pulling both Yes and NO records. I want the code to pull only the YES (where data presents in ALL three days, not just across one or two days) . Thanks again for the help.

Acct nm	Data present in 1st date	Data present in 2nd day	Data present in 3rd day	Query should pull ?
1234	aa	ab	abc	Yes
4567	aa			No
7890		bc	bb	No
1357			bb	No
4802	aa	aa	aa	yes

[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.

[mkeintz · Accepted solution]

In your HAVING CLAUSE  add

  AND COUNT(DISTINCT DATE)=3

[User_2024]

this helped thank you.

[FreelanceReinh]

Hello @User_2024,

---

@User_2024 wrote:

this helped thank you.

---

In this case it would be fair and help later readers if you marked the helpful reply as the accepted solution, not your own "thank you" post. Could you please change that? It's very easy: Select the appropriate post as the solution after clicking "Not the Solution" in the option menu (see icon below) of the current solution.

[User_2024]

Thanks for indicating me, I am relatively new to the page and wasn't aware of the changing option. I was actually meaning to CHOOSE the solution as solution and not my own post. I corrected it now. Hope it helps.