Filter to previous month - SQL

[catkat96]

I need to add a filter that would grab only data from the previous month. 

My code is the following: 

proc sql;
create table WANT as
select distinct id as id 
, max(date) format=ddmmyy10.
from have
where date between "01Apr2021"d and "31Apr2021"d
group by id;
quit;

As you can see it's pretty simple, but I would like to not have the dates hardcoded so it can be run automatically. 

I would like to grab all distinct ID from HAVE from the previous month

[Kurt_Bremser]

Do you want the previous month counted from today, or from the max date in the dataset?

Anyway, see here:

proc sql;
create table WANT as
  select distinct
    id as id 
    , max(date) format=ddmmyy10.
  from have
  where date between intnx('month',today,-1,'b') and intnx('month',today(),-1,'e')
  group by id
;
quit;

[PGStats]

To extract all the data from the previous month into table WANT:

proc sql;
create table WANT as
select * 
from have
where date between intnx("month", today(), -1, "beginning") and intnx("month", today(), -1, "end");
quit;

[PGStats]

Note, if you want to process the extracted data further, you might be better to create a view instead of a table ( create view WANT as ... ) and then access that view in a subsequent query (create table meanProfits as select id, mean(profit) as meanProfit from WANT group by id; )