Solved
Contributor
Posts: 51

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

Accepted Solutions
Solution
‎10-13-2013 02:23 PM
Posts: 5,543

## 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

All Replies
Solution
‎10-13-2013 02:23 PM
Posts: 5,543

## 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
• 196 views
• 0 likes
• 2 in conversation