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

Question

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:

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

score 3 · Accepted Answer · answered Mar 06 '17 at 16:24

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;

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

1 Answers1