BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
t30
Fluorite | Level 6 t30
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

8 REPLIES 8
Reeza
Super User

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
Fluorite | Level 6 t30
Fluorite | Level 6
%let last3months = %sysfunc(intnx("month", today(), -3, "E"), date9.);

Tried this but doesn't seem to work though 

Reeza
Super User

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
Fluorite | Level 6 t30
Fluorite | Level 6

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

Reeza
Super User

@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
Fluorite | Level 6 t30
Fluorite | Level 6
Thanks, will remember to post the log next time as well.
Reeza
Super User

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
Fluorite | Level 6 t30
Fluorite | Level 6
I see, thanks for that tips, should be able to change the other parametres now!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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