How to get the date of 1st day of the last x months?

[t30]

I'm trying to populate a PROC SQL with some SAS date9. dates.

For example;

rsubmit;
% let todaysDate = %sysfunc(today(), date9.);
%put %todaysDate;
proc sql;
 create table want
 as
 select distinct *,
     case
         when lstpur between '&last3months'd and '&todaysdate'd then 'Active past3m'
        when  lstpur between '&last4months'd and '&todaysdate'd then 'Active past4m'
        else 'not active'
end as Usage_group
from have;
quit;
endrsubmit;

The trouble is to generate that last3months and last4months date. I may need the last3months or last4months as the first date of the month or last date of the month.

How do I get these dates?

Thanks!

[Reeza]

Use the INTNX function, with the fourth parameter which allows you to specify an alignment, ie end or beginning of the month. The documentation has an example of this. 

[t30]

%let last3months = %sysfunc(intnx("month", today(), -3, "E"), date9.);

Tried this but doesn't seem to work though 

[Reeza]

---

t30 wrote: Tried this but doesn't seem to work though 

---

What does that mean?

What do you get and what are you expecting? Exactly.

[t30]

I was expecting a macro will be created using the combination of sysfunc and intx

[Reeza]

---

@t30 wrote:

I was expecting a macro will be created using the combination of sysfunc and intx

---

That's not really informative. You didn't post what you got, which would have been a log full of errors that you should have included in your post. The phrase 'not working' doesn't convey any information besides something being broken. We already know that, that's why you're posting a question in the first place. In the future please be more descriptive. 

[t30]

Thanks, will remember to post the log next time as well.

[Reeza · Accepted solution]

1. You forgot a %SYSFUNC() around TODAY() - it is a function as well and all need to be listed.

2. There are no quotes around parameters in a macro function call.

%let last3months = %sysfunc(intnx(month, %sysfunc(today()), -3, e), date9.);
%put &last3months;

[t30]

I see, thanks for that tips, should be able to change the other parametres now!