DATA Step, Macro, Functions and more

SAS dates - within last 3 months

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

SAS dates - within last 3 months

Hello,

 

I have a data step whereby I'm selecting around 50,000 customers. In the output file I have date fields (DATE9.) and I want to create an if statement that will create a value for only those dates which fall in the last 3 months. So far I have the below, please advise where I'm going wrong and how I can do this?

 

IF TXN_DATE BETWEEN TODAY() AND TODAY()-3 THEN DO;R_SCORE=50;END;

 

Thanks


Accepted Solutions
Solution
‎03-10-2017 07:35 AM
Super User
Posts: 5,518

Re: SAS dates - within last 3 months

[ Edited ]
Posted in reply to CamRutherford

SAS stores dates as referring to a particular day.  So TODAY()-3 is three days earlier.  Change it to something like TODAY()-90 and you should be fine.

 

It's possible (can't test it right now) that you can't use BETWEEN in an IF statement.  That's easy enough to change:

 

if TODAY()-90 <= TXN_DATE <= TODAY() then R_SCORE=50;

View solution in original post


All Replies
Solution
‎03-10-2017 07:35 AM
Super User
Posts: 5,518

Re: SAS dates - within last 3 months

[ Edited ]
Posted in reply to CamRutherford

SAS stores dates as referring to a particular day.  So TODAY()-3 is three days earlier.  Change it to something like TODAY()-90 and you should be fine.

 

It's possible (can't test it right now) that you can't use BETWEEN in an IF statement.  That's easy enough to change:

 

if TODAY()-90 <= TXN_DATE <= TODAY() then R_SCORE=50;

Contributor
Posts: 60

Re: SAS dates - within last 3 months

Posted in reply to Astounding
Legend - thank you
Super User
Super User
Posts: 7,997

Re: SAS dates - within last 3 months

Posted in reply to Astounding

Just to note @Astounding, that is assuming a base value of 30 days per month.  That may not be entirely acurate.  I would suggest using the function for it:

 

if intnx('month',today(),-3) <= txn_date <= today() then r_score=50;
Super User
Posts: 7,868

Re: SAS dates - within last 3 months

[ Edited ]
Posted in reply to CamRutherford

Don't shout your code. Normal speech is sufficient (don't write code in all capitals, for readability)

 

The between operator is only available in where conditions, use this instead:

if intnx('month',today(),-3,'same') <= txn_date <=today()
then do;
  r_score = 50;
end;

Assuming that txn_date is a SAS date value, of course

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 192 views
  • 0 likes
  • 4 in conversation