BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mtgkooks
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

 

 

View solution in original post

4 REPLIES 4
Reeza
Super User
You likely want INTNX + NWKDOM.
TomKari
Onyx | Level 15

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
Fluorite | Level 6
Thanks a lot, Tom!
I will give this a try and let you know how it turns out.
jebjur
SAS Employee

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 6748 views
  • 1 like
  • 4 in conversation