BookmarkSubscribeRSS Feed
mansour_ibrahim
Obsidian | Level 7

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

 

5 REPLIES 5
Reeza
Super User

@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?

mansour_ibrahim
Obsidian | Level 7

what are you trying to do here, in words? I do not know for the moment

Tom
Super User Tom
Super User

@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?

Reeza
Super User

@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?" 🙂

Tom
Super User Tom
Super User

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.

 

 

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
  • 5 replies
  • 1979 views
  • 0 likes
  • 3 in conversation