DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor t30
Occasional Contributor
Posts: 14
Accepted Solution

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

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!


Accepted Solutions
Solution
‎07-31-2017 12:17 AM
Super User
Posts: 19,855

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

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;

View solution in original post


All Replies
Super User
Posts: 19,855

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

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. 

Occasional Contributor t30
Occasional Contributor
Posts: 14

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

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

Tried this but doesn't seem to work though 

Super User
Posts: 19,855

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


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.

Occasional Contributor t30
Occasional Contributor
Posts: 14

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

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

Super User
Posts: 19,855

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


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. 

Occasional Contributor t30
Occasional Contributor
Posts: 14

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

Thanks, will remember to post the log next time as well.
Solution
‎07-31-2017 12:17 AM
Super User
Posts: 19,855

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

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;
Occasional Contributor t30
Occasional Contributor
Posts: 14

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

I see, thanks for that tips, should be able to change the other parametres now!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 187 views
  • 0 likes
  • 2 in conversation