topic Re: logic to find data on particular number of days in SAS Programming

logic to find data on particular number of days

User_2024 — Wed, 31 Jul 2024 19:08:56 GMT

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

Re: logic to find data on particular number of days

SASJedi — Wed, 31 Jul 2024 20:47:26 GMT

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

Re: logic to find data on particular number of days

ballardw — Wed, 31 Jul 2024 21:15:03 GMT

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

Re: logic to find data on particular number of days

User_2024 — Thu, 01 Aug 2024 00:53:24 GMT

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

Re: logic to find data on particular number of days

mkeintz — Thu, 01 Aug 2024 01:18:19 GMT

In your HAVING CLAUSE add

  AND COUNT(DISTINCT DATE)=3

Re: logic to find data on particular number of days

User_2024 — Thu, 01 Aug 2024 02:31:22 GMT

this helped thank you.

Re: logic to find data on particular number of days

FreelanceReinh — Thu, 01 Aug 2024 06:08:18 GMT

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.

Re: logic to find data on particular number of days

User_2024 — Thu, 01 Aug 2024 12:30:22 GMT

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.