A couple comments. It's still August 28 here, so over here you would want
where datepart (TRANSACTIONDATE) >= "29jul2020"d
Notice it's >=, not <, since you apparently want the most recent month.
But why bother applying the datepart function to every observation, when you can do the equivalent test for a datetime cutoff. I.e., the condition could be modified to:
where TRANSACTIONDATE >= "29jul2020:00:00:00"dt
So how to generate the 29jul2020:00:00:00? You could put this in the where clause:
where TRANSACTIONDATE >= intnx(dtmonth,intnx('dtday',datetime(),1),-1,'same')
The internal INTNX takes the current datetime and adds 1 day (unit DTDAY) to it, and by default aligns the result to the beginning of the day, i.e. 29AUG2020:00:00:00. Then the external INTNX goes back 1 month (minus 1 DTMONTH), from that value, but is told to align it to the same day-of-month (rather than defaulting to the beginning of the month). So it generates the datetime for 29JUL2020:00:00:00.
But wait, there's more. Instead of telling SQL to calculate these nested functions once per observation to repeatedly generate a constant, you can use macro language to calculate the constant just once, and use the resulting macrovar in the sql, as in:
%let midnight_tonight=%sysfunc(intnx(dtday,%sysfunc(datetime()),1));
%let midnight_one_month_prior=%sysfunc(putn(%sysfunc(intnx(dtmonth,&midnight_tonight,-1,same)),datetime20.));
%put &=midnight_one_month_prior;
proc sql noprint;
create table test1 as Select distinct quote_number(alphnumeric),
effective, TRANSACTIONDATE
from Libname.table
where TRANSACTIONDATE >= "&midnight_one_month_prior"dt
and quotenumber like 'Q%'
order by transactiondate desc;
Quit;
So this provides the correct filter, and minimizes the computing burden while doing it.
