CODEWITHSUNDEEP
sasproc-sql
Martin Reindl
Martin Reindl

Reputation: 1603

Comparing two date variables in SAS in a proc sql WHERE clause

I am using SAS Enterprise guide and want to compare two date variables:

My code looks as follows: 

proc sql; 
    CREATE TABLE observations_last_month AS
    SELECT del_flag_1,
           gross_exposure_fx, 
           reporting_date format=date7., 
           max(reporting_date) AS max_date format=date7.
    FROM &dataIn. 
    WHERE reporting_date = max_date; 
quit;

If I run my code without the WHEREstatement I get the following data:

enter image description here

However, when I run the above code I get the following error messages: 

ERROR: Expression using (=) has components that are of different data types.
ERROR: The following tables were not found in the contributing tables: max_date.

What am I doing wrong here? Thanks up front for the help

Upvotes: 0

Views: 1990

Answers (1)

Tom
Tom

Reputation: 51566

If you want to subset based on an aggregate function then you need to use HAVING instead of WHERE. If you want to refer to a variable that you have derived in your query then you need to use the CALCULATED keyword (or just re-calculate it).

proc sql; 
  CREATE TABLE observations_last_month AS
    SELECT del_flag_1
         , gross_exposure_fx
         , reporting_date format=date7.
         , max(reporting_date) AS max_date format=date7.
    FROM &dataIn. 
    HAVING reporting_date = CALCULATED max_date
  ; 
quit;

Upvotes: 3

Related Questions