Applying a date condition to my date conditions

[mtgkooks]

I have a SAS EG 7.15 project with a PROC SQL statement that pulls data from a table based on a hard-coded date range condition:

rundate between '01Aug2018:0:0:0'dt and '15Aug2018:0:0:0'dt

This project gets run on the first & third Wednesdays of each month. On the first Wednesday of the month, the date range is changed to pull data from the 16th through the end of the previous month. On the third Wednesday of the month, the date range is again changed to pull data from the 1st - 15th of the current month.

I would like to be able to automate this process. However, I'm unsure how I can apply a condition to my condition; for example, how to tell SAS that if it's the first Wednesday of October, the above line needs to read:

rundate between '16Sep2018:0:0:0'dt and '30Sep2018:0:0:0'dt

and then, on the third Wednesday of October, it would read:

rundate between '01Oct2018:0:0:0'dt and '15Oct2018:0:0:0'dt

I've explored using the NWKDOM() function to no avail; it seems like this situation might be calling for a %macro, with which I have no experience.

Any help that anyone can provide will certainly be much appreciated!

[Reeza]

You likely want INTNX + NWKDOM.

[TomKari · Accepted solution]

First, I'm assuming that you have a query that includes the between filter.

Step 1, create a code node in your project, and add this code:

data Temp1;
	format ThisDate datetime.;
	format StartDate datetime.;
	format EndDate datetime.;
	ThisDate = datetime();
	ThisDay = day(datepart(ThisDate));

	if ThisDay <=14
		then do; /* Must be first Wednesday of month */
		EndDate = intnx("dtmonth", ThisDate, -1, "end");
		StartDate = dhms(mdy(month(datepart(EndDate)), 16, year(datepart(EndDate))), 0, 0, 0);
	end;
	else do;
		StartDate = dhms(mdy(month(datepart(ThisDate)), 1, year(datepart(ThisDate))), 0, 0, 0);
		EndDate = dhms(mdy(month(datepart(ThisDate)), 15, year(datepart(ThisDate))), 23, 59, 59);
	end;

	output;
	call symput("StartDate", put(StartDate, datetime.));
	call symput("EndDate", put(EndDate, datetime.));
run;

%put &=StartDate.;
%put &=EndDate.;

Run it, and play with it, and see if the results are what you want.

Step 2, modify your query, changing the hardcoded dates to look like:

"&StartDate."dt
"&EndDate."dt

Try the whole thing, and make sure it does what you want. Once it does, you can greatly shorten the code node to:

data _null_;
	ThisDate = datetime(); ThisDate = "27sep2018:00:00:00"dt;
	ThisDay = day(datepart(ThisDate));

	if ThisDay <=14 then do;

		/* Must be first Wednesday of month */
		EndDate = intnx("dtmonth", ThisDate, -1, "end");
		StartDate = dhms(mdy(month(datepart(EndDate)), 16, year(datepart(EndDate))), 0, 0, 0);
	end;
	else do;
		StartDate = dhms(mdy(month(datepart(ThisDate)), 1, year(datepart(ThisDate))), 0, 0, 0);
		EndDate = dhms(mdy(month(datepart(ThisDate)), 15, year(datepart(ThisDate))), 23, 59, 59);
	end;

	call symput("StartDate", put(StartDate, datetime.));
	call symput("EndDate", put(EndDate, datetime.));
run;

and you should be good to go.

Tom

[mtgkooks]

Thanks a lot, Tom!
I will give this a try and let you know how it turns out.

[jebjur]

Here is one more idea... you can use a macro to conditionally run your PROC SQL code based on whether the current date is either the 1st Wednesday or the 3rd Wednesday of each month. Something similar to the following:

%macro abc;

%let td=%sysfunc(today()); /* today's date */
%let first=%sysfunc(nwkdom(1,4,%sysfunc(month(&td)),%sysfunc(year(&td)))); /* first wednesday of month */
%let third=%sysfunc(nwkdom(3,4,%sysfunc(month(&td)),%sysfunc(year(&td)))); /* third wednesday of month */

%if &td=&first %then %do;

proc sql;
create table want as select * from have
where date between '16AUG2018:0:0:0'dt and '30AUG2018:0:0:0'dt;
quit;
%end;

%else %if &td=&third %then %do;

proc sql;
create table want as select * from have
where date between '1SEP2018:0:0:0'dt and '16SEP2018:0:0:0'dt;
quit;
%end;

%mend;

%abc