Hello,
I have requests mysql, i want to translate in sas :
1/ date> DATE_FORMAT(now(), "%Y-%m-01")
i make this ==>> date> intnx('month',today(),0,'b') ; is't correct ???
2/ MOIS >= DATE_FORMAT( DATE_SUB( NOW( ) , INTERVAL 1 MONTH) , '%Y-%m-01');
i make this ===>> mois > INTNX('month',today(),1,'b'); is't correct ???
Thank you
@mansour_ibrahim wrote:
Hello,
I have requests mysql, i want to translate in sas :
1/ date> DATE_FORMAT(now(), "%Y-%m-01")
i make this ==>> date> intnx('month',today(),0,'b') ; is't correct ???
2/ MOIS >= DATE_FORMAT( DATE_SUB( NOW( ) , INTERVAL 1 MONTH) , '%Y-%m-01');
i make this ===>> mois > INTNX('month',today(),1,'b'); is't correct ???
Thank you
Are you getting the values you expect?
Pretend we dont' know MYSQL, what are you trying to do here, in words?
what are you trying to do here, in words? I do not know for the moment
@mansour_ibrahim wrote:
what are you trying to do here, in words? I do not know for the moment
Then how will you know whether you have gotten the right answer?
Perhaps you need to post a question on a MySQL site to find out the meaning of the original syntax?
@mansour_ibrahim wrote:
what are you trying to do here, in words? I do not know for the moment
Whenever I'd say that as a kid, my dad would respond, "If you don't know, who should?" 🙂
As best as I can tell from this man page https://www.w3schools.com/sql/func_mysql_date_format.asp the meaning of DATE_FORMAT() is more like a FORMAT statement than like an actual function. So something like
select DATE_FORMAT(now(), "%Y-%m-01") as mydatevar
, DATE_FORMAT( DATE_SUB( NOW( ) , INTERVAL 1 MONTH) , '%Y-%m-01') as mydatevar2
would translate into creating a variable with today's date but with a format attached to it that displays it as if it was the first day of the month. You could possible build such a format, but why not just use one of the existing date formats that does NOT display the day of the month so that it doesn't matter what the day of the month really is? The DATE_SUB() function subtracts intevals dates so you need to use INTNX() to simulate that.
select date() as mydatevar format=yymmd7.
, intnx('month',date(),-1,'s') as mydatevar2 format=yymmd7.
But it might just be easier to just set the value to the first day of the month instead then you could use the YYMMDDd10. format (the last D means to use dash as separator) to have it display that date in YYYY-MM-DD format.
select intnx('month',date(),0,'b') as mydatevar format=yymmddd10.
, intnx('month',date(),-1,'b') as mydatevar2 format=yymmddd10.
Note that you need to use a negative number in your INTNX() calls since DATE_SUB() means SUBTRACT. There is a separate function, DATE_ADD(), that is used to move forward in time.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.