Hi,   Thank you for weighing in, but unfortunately, I am still having issues.    I am trying to set up an automation that will work like this: (1) identify the maximum date in a data table (end date), (2) use INTNX to capture the 12-month prior date (start date), (3) pass the start-date and the end-date to 2 separate macro variables, and (4) set the start-date and end-date (macro variables) in a WHERE clause so it returns rows only within these 2-dates.   When I manually set the macro variable as follows, the query works just fine: %let start_dt = '2020-12-31 00:00:00'; %let end_dt = '2024-01-01 00:00:00';   However, when I used the following code, the query is not returning any rows.  Because the only difference is in how the date fields are being passed to the query, I am suspecting the macro variable is not resolving properly.   PROC SQL; SELECT END_DATE FORMAT=BEST32. INTO: END_DATE FROM DATA_DATE_END; QUIT;   I am having the same issue when I used the code you had shared below.    Please let me know if you have any further questions.  I don't know if this is material or not, but I am using Enterprise Guide to query a table in RedShift using an ODBC connection.     Thank you. ... View more

THANK YOU!!!  This finally worked (and yes, I double, triple checked this time around:-) ).  Using unformatted macro vars was the key.  It worked fine without trimming the spaces.   ... View more

You were right. This was not the issue. The code was run with another macro variable that used to work properly. I am going to try the unformatted version per your instructions and see how that goes. Thanks again for your help. ... View more

I figured out the issue, I forgot to put a period when calling the macro variable in the WHERE clause, I modified the syntax as follows and it works exactly as intended.   where (&STRT_DATE. <= a.veh_first_quot_tstmp_local and a.veh_first_quot_tstmp_local <= &END_DATE.)   Thanks again for all the thought partnership on this.   Don ... View more

Tom,   You are right.  The date resolved to the correct format in the macro variable.  I should have checked the Macro Variable Viewer before posting in the first place.  My bad.  I am trying to set up an automation where my code automatically pulls rolling 12-month of data based on the date field.  I have approached this as follows: 1. My code checks the max date that is in the data table which represents the most recent month of data (i.e., the "END_DATE" for my purposes) 2. Using the DTTIME option in INTNX I calculate the 12-month PRIOR date, (i.e., the "BEGIN_DATE" for my purposes) 3. I create %END_DATE and %BEGIN_DATE macro variables based on steps #1 and #2 above.  This way when I run this code next month, it will pull the appropriate rolling 12-month window without manually having to specify it each time. 4. I pass the macro variables in the WHERE statement in PROC SQL to only return data rows that are between %BEGIN_DATE and %END_DATE   This is the syntax for the WHERE clause I am using: where (&STRT_DATE <= a.veh_first_quot_tstmp_local and a.veh_first_quot_tstmp_local <= &END_DATE)   However, when I pass the macro variable in step #4, I am getting the following error in my query which I am not able to decipher.  The date format in the source table as well as the macro variables are both DATE25.6.  In my data,  %END_DATE is 30JAN2025:00:00:00.000000 and %BEGIN_DATE is 01FEB2024:00:00:00.000000   ERROR: CLI prepare error: [Redshift][ODBC Driver][Server]42601:ERROR: syntax error at or near "FEB2024" in context "where (01FEB2024", at line 1 LINE 1, POSITION 50: ..._id and a.hhld_exp_cnt = b.hhld_exp_cnt) where (01FEB2024:00... ... View more

Thank you.  That worked, but I have another issue related to my original post.     I need to pass the resulting date to a macro variable and I did that using the following code: PROC SQL;      SELECT RESULTING_DATE      INTO: RESULTING_DATE      FROM TIME_DATA; QUIT;   However, when I did that, it resolves to 30NOV2024.  Could you please let me know how I could reformat the macro variable %RESULTING_DATE to be in DATETIME25.6 format.   Thanks again for your assistance.   Don ... View more

mkeintz, that WORKED!  Thank YOU!!!!!!! I took the easy way out initially and set the zip codes to numeric in the source table (because US zips don't contain characters) and it seemed to run faster than when they were set as a character variable.  Not sure if there is a rational explanation for this or if I was just imagining things :-).  Regardless, this ran at an incredibly faster rate than without using HASH tables.  Thanks once again.  ... View more