topic Re: Filter to previous month - SQL in SAS Programming

Filter to previous month - SQL

catkat96 — Mon, 24 May 2021 08:58:42 GMT

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

Re: Filter to previous month - SQL

Kurt_Bremser — Mon, 24 May 2021 10:27:19 GMT

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;

Re: Filter to previous month - SQL

PGStats — Mon, 24 May 2021 17:12:01 GMT

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;

Re: Filter to previous month - SQL

PGStats — Mon, 24 May 2021 17:17:48 GMT

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; )