@donspaul wrote:
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.
Thank you for explaining, there's no way anyone can give valid advice without these details. You want &END_DATE to be unformatted. You want &START_DATE to be unformatted. Then everything works, assuming your date variable is NUMERIC date variable and has any format you want or even if it is unformatted.
PROC SQL;
SELECT END_DATE FORMAT=BEST32.
INTO: END_DATE
FROM DATA_DATE_END;
QUIT;
/* Step 2 */
data _null_;
set have;
/* NOTE WE USE UNFORMATTED &END_DATE */
start_date=intnx('dtmonth',&end_date,-12,'b');
/* NOTE &START_DATE is unformatted */
call symputx('start_date',start_date);
run;
/* Step 3 */
data want;
/* NOTE VARIABLES CAN BE FORMATTED IN THE DATA SET */
set yourbigdataset;
/* NOTE THIS WORKS WITH UNFORMATTED MACRO VARIABLES */
if datevariable = &start_date or date variable=datepart(&end_date) then output;
run;
If datevariable is actually a datetime variable, we can modify the code to work with that as well.
... View more
