filter condition

Posted on 2011-05-05
Last Modified: 2013-11-16
Hi There,
Trying to run below query getting error below dateformat is yymmdd8
16         proc sql;
17          create table myams as(
18            select * from aa.TMP
19             where date='2011-04-19');
ERROR: Expression using equals (=) has components that are of different data types.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
Question by:sam2929
    LVL 9

    Accepted Solution

    Try using this example:

    proc sql;
             create table myams as(
               select * from aa.TMP
                where date='19APR2011'D);

    LVL 7

    Expert Comment

    As bradanelson pointed out above, you're comparing a date variable to a literal character string.  SAS stores date variables as numbers--the number of days before or after January 1, 1960.  You'll  often see dates referred to as 'date values'.    0 is 1/1/1960 and 18752 is 5/5/2011.

    If you know the number of days then you can use a number in a comparison or assignment statement.  But it's easier to specify a date value as '19APR2011'D.   The trailing D (or d) tells SAS to convert the literal into a date value.  The format must always be 'ddmmmyyyy'd although a two-digit yy will work but be very careful with those nowadays.

    There are lots of good SAS conference papers written about SAS and dates.  If you're going to be working with dates quite a bit then it will be very much worth your while to read some of them.  This is a great web site for user-written SAS documentation--

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Introduction If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later ( by BrandonGalderisi (h…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now