topic Re: Comparing date in where clause in SAS Procedures

Comparing date in where clause

SAS_NUBI2015 — Thu, 30 Apr 2015 11:10:26 GMT

Hello together,

I'm new in SAS and working with SAS EG. I try to extract data from dwh for the last two years. The part of sql-statement in where-clause looks like that:

WHERE t1.V_Date_Create_At >= (today() -

(substr(today(),1,2) ||'.'||substr(today(),4,2)||'.'||(substr(today(),7,4)-2));

I got the error 79-322. Has anyone some clues how shall I modify this statement that runs?

Thank you for help

Re: Comparing date in where clause

BrunoMueller — Thu, 30 Apr 2015 14:28:49 GMT

The TODAY() function will return the current date as a number of days since 01Jan1961, see also SAS(R) 9.4 Language Reference: Concepts, Fourth Edition

SAS has a function called INTNX which allows you to subtract a time interval like DAY, MONTH, YEAR an so on, by default the date returned is always the beginning of an interval to subtract 2 years and go to the same day see log below:

32	data _null_;
33	now = today();
34	now2years = intnx("YEAR", now, -2 , "SAME");
35
36	format now: date9.;
37	putlog _all_;
38	run;

now=30APR2015 now2years=30APR2013 _ERROR_=0 _N_=1

Re: Comparing date in where clause

SAS_NUBI2015 — Fri, 01 May 2015 13:44:06 GMT

Hello Bruno,

Firstly thank your very much indeed for matched solution. May I ask you my question from another perspektive:

the format of date in dwh is like DD.MM.YYYY. As I understood the output-format is DDMMMYYYY. Because I usually use SAS Enterprise Guide (also only via GUI and not directly in stored process sql) I would ask you if is there any possibility to pre-format the desired date (here the date is two years ago from today) in input field? Some thing with output format DD.MM.YYYY directly in WEHRE Clause? As I researched in " SAS Date Formats and Functions" (SAS/ETS(R) 9.2 User's Guide) I unfortunately could not find the right syntax for my output format.

Thank you very much.

Re: Comparing date in where clause

ballardw — Fri, 01 May 2015 14:48:15 GMT

If you are asking "can I put a specific date value" in a where clause the answer is yes and the concept is called a date literal.

If you were to look for dates after 10 March 2014 the syntax would be
where datevariable > '10MAR2014'd.

Note that the use of day, 3 letter month abbreviation then year is required and has nothing to do with the format applied to the variable. The quotes plus the letter d tell SAS to interpret the string as a date value. Similar coding allows specific time and datetime values.

The date literals can be used in any place a value would be used.

Re: Comparing date in where clause

SAS_NUBI2015 — Sun, 03 May 2015 13:43:39 GMT

Hello Ballardw,

Thank you for your clue.

My question was based on yours also the recommendation of Bruno in such context:

//=========================================================

PROC SQL;

CREATE TABLE WORK.Company AS

SELECT t1.date, .... /* "date format" in "SAS dwh" is like that: 01.05.2015 */

....

FROM tbale1 t1, table2 t2, table3 t3

WHERE t1.id = t2.id

AND t2.id = t3.id

AND t1.date >= intnx("YEAR", today():MMDDYYP10. , -2 , "SAME"); /*the date of last two dates*/

QUIT;

//=========================================================

The expression "t1.date >= intnx("YEAR", today():MMDDYYP10. , -2 , "SAME");" shall demonstrate the dates in the last two years from the today-aspect with date-format "DD.MM.YYYY".

How is that possible to express such where-clause in SAS?

Thank you very much in advance.

Re: Comparing date in where clause

Tom — Sun, 03 May 2015 14:14:21 GMT

The format attached to a value does not change the value that is used in evaluating expressions like <, just how it is displayed.

Let us assume that your variable V_Date_Create_At is a date variable and you have a user prompt that is stored into the macro variable START.

Since macro variables are always strings you either need to supply the value as a date literal, or in an agreed upon date format.

Let's assume that the user enters dates in the MM.DD.YYYY format you mentioned. Then your WHERE clause should look something like

WHERE t1.V_Date_Create_At >= input("&start",mmddyy10.)

If you want all dates starting with 2 years BEFORE the date they entered then add the INTNX() function.

WHERE t1.V_Date_Create_At >= intnx('year',input("&start",mmddyy10.),-2,'same')

However if you are saying that your variable V_Date_Create_At is a character string with values that look like dates in mdy order ('03.10.2014' for example) then inequality expressions like >= will not work as expected. So you will need to convert the string to a date to apply the selection criteria.

WHERE input(t1.V_Date_Create_At,mmddyy10.) >= intnx('year',input("&start",mmddyy10.),-2,'same')

Re: Comparing date in where clause

SAS_NUBI2015 — Mon, 04 May 2015 16:09:27 GMT

Hello Tom,

Thank you. I put the expression in the filter of my query based on the required table in this form:

WHERE t1.V_Date_Create_At >= intnx('year',input(today(),MMDDYYP10.),-2,'same');

// Matched results should seem like that:

Today: 04.05.2015

Date of last 2 years I need through my query:

04.05.2013

05.05.2013

06.05.2013

etc.

However it caused some errors because I only compare necessary datas in the last two years beginning from today.

What is wronge in this statement?

Thank you.

Re: Comparing date in where clause

BrunoMueller — Mon, 04 May 2015 20:05:15 GMT

change

WHERE t1.V_Date_Create_At >= intnx('year',input(today(),MMDDYYP10.),-2,'same');

WHERE t1.V_Date_Create_At >= intnx('year', today(), -2, 'same');

Since the TODAY() function already returns the date as a number, there is not need to do any type conversion using the INPUT() function.

Re: Comparing date in where clause

Kurt_Bremser — Tue, 05 May 2015 05:56:45 GMT

I think you still have a misunderstanding of how SAS works with date (and time) values.

Thoroughly read SAS(R) 9.2 Language Reference: Concepts, Second Edition

Re: Comparing date in where clause

SAS_NUBI2015 — Wed, 06 May 2015 13:25:51 GMT

Thank you all again for your help:-)