Using Variables To Form Dates Used In Where Clause

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Using Variables To Form Dates Used In Where Clause

Hi friends,

While I have four variables representing the months (Beg & End) and years (Beg & End) what could be the effective way of designing a query to select the requisite records in the following case:

%LET MonthBeg = 7;

%LET MonthEnd = 6;

%LET YearBeg = 2010;

%LET YearEnd = 2012;

...

...

PROC SQL;

CREATE TABLE REZULT AS SELECT * FROM WORK.MAJ_DATA WHERE DUE_DATE BETWEEN (&MonthBeg and &YearBeg) AND (&MonthEnd and &YearEnd);

QUIT;

Please see if any of you experts could help by providing the correct way to use the variables in the where clause to determine and use the data filter effectively.

Thanx in advance.


Accepted Solutions
Solution
‎10-13-2013 02:23 PM
Respected Advisor
Posts: 4,934

Re: Using Variables To Form Dates Used In Where Clause

Posted in reply to FarazA_Qureshi

Assuming that DUE_DATE is a DATE and not a DATETIME, you should use:

%LET MonthBeg = 7;
%LET MonthEnd = 6;
%LET YearBeg = 2010;
%LET YearEnd = 2012;

PROC SQL;
CREATE TABLE REZULT AS
SELECT *
FROM WORK.MAJ_DATA
     WHERE DUE_DATE BETWEEN
          mdy(&MonthBeg,1,&YearBeg) AND
          intnx("MONTH",mdy(&MonthEnd,1,&YearEnd),0,"END");
QUIT;

PG

PG

View solution in original post


All Replies
Solution
‎10-13-2013 02:23 PM
Respected Advisor
Posts: 4,934

Re: Using Variables To Form Dates Used In Where Clause

Posted in reply to FarazA_Qureshi

Assuming that DUE_DATE is a DATE and not a DATETIME, you should use:

%LET MonthBeg = 7;
%LET MonthEnd = 6;
%LET YearBeg = 2010;
%LET YearEnd = 2012;

PROC SQL;
CREATE TABLE REZULT AS
SELECT *
FROM WORK.MAJ_DATA
     WHERE DUE_DATE BETWEEN
          mdy(&MonthBeg,1,&YearBeg) AND
          intnx("MONTH",mdy(&MonthEnd,1,&YearEnd),0,"END");
QUIT;

PG

PG
Contributor
Posts: 51

Re: Using Variables To Form Dates Used In Where Clause

Excellent introduction of intnx mdy functions.

Thanx PG!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 171 views
  • 0 likes
  • 2 in conversation