Help using Base SAS procedures

Comparing date in where clause

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Comparing date in where clause

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


Accepted Solutions
Solution
‎05-04-2015 04:05 PM
SAS Super FREQ
Posts: 682

Re: Comparing date in where clause

Hi

change

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

to

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.

View solution in original post


All Replies
SAS Super FREQ
Posts: 682

Re: Comparing date in where clause

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

Occasional Contributor
Posts: 12

Re: Comparing date in where clause

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.

Super User
Posts: 10,463

Re: Comparing date in where clause

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.

Occasional Contributor
Posts: 12

Re: Comparing date in where clause

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.

Super User
Super User
Posts: 6,495

Re: Comparing date in where clause

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')

Occasional Contributor
Posts: 12

Re: Comparing date in where clause

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.

Solution
‎05-04-2015 04:05 PM
SAS Super FREQ
Posts: 682

Re: Comparing date in where clause

Hi

change

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

to

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.

Occasional Contributor
Posts: 12

Re: Comparing date in where clause

Thank you all again for your help:-)

Super User
Posts: 6,927

Re: Comparing date in where clause

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 13256 views
  • 7 likes
  • 5 in conversation