Desktop productivity for business analysts and programmers

MDY Function

Reply
Contributor
Posts: 50

MDY Function

I have a variable date1 which shows, for example, 01JAN2010 00:00:00 and want it to show up (1/1/10)

Do I do MDY(date1, 1, date1)

 

How can I get it so that I can use the variable to populate to that format?

Super User
Posts: 22,823

Re: MDY Function

Posted in reply to mmagnuson

That's a DATETIME not DATE field. First use DATEPART to get the date portion and then apply your functions. 

 

Any relation to the almost identical post here?

https://communities.sas.com/t5/SAS-Enterprise-Guide/Query-filter-WEEK-and-similar-date-time-function...

 

Contributor
Posts: 50

Re: MDY Function

Ah I did see that post but forgot to include my other question. Whoops!

 

Is it possible to use both datepart and mdy in the same statement? I.e. creating a computed column?

Super User
Posts: 22,823

Re: MDY Function

Posted in reply to mmagnuson

mmagnuson wrote:

Ah I did see that post but forgot to include my other question. Whoops!

 

Is it possible to use both datepart and mdy in the same statement? I.e. creating a computed column?


Yes, you can nest functions in SAS (and most other languages).

 

 

Super User
Posts: 6,534

Re: MDY Function

Posted in reply to mmagnuson

Changing the form  in which a variable prints requires a format, not a function.  So convert from a datetime to a date:

 

var = datepart(var);

 

Then apply a format:

 

format var mmddyys10.;

Super User
Posts: 22,823

Re: MDY Function

Posted in reply to mmagnuson

Looking at what you're actually asking though, you don't need to do any of that. 

 

You need to either :

 1. Create a date variable and apply the correct format

2. Apply the appropriate format to the datetime variable if you don't care about the time, ie dtmmddyy10. (check docs for exact, this is just a guess)

Super User
Super User
Posts: 7,845

Re: MDY Function

Posted in reply to mmagnuson

The MDY() function is for converting three numbers, month, day and year, into a date.

Your example value is a datetime value.  You can just use the DATEPART() function to convert it to a date value.

 

How you display it depends on the format you attach to the variable.  It seems that currently you have the DATETIME format attached, which is a good format for a datetime value. There are formats that can display a datetime value in Y,M,D order if you want, but I don't think there are any that can do it in M,D,Y order.  But you could build you own using PROC FORMAT.

 

Or you could convert your datetime to a date, then there are many more choices for formats you can use to display the data. MMDDYY or DDMMYY or YYMMDD or DATE etc.

 

Contributor
Posts: 50

Re: MDY Function

I also then want the date1 to be ("month of date1' , 1, 'year of date1')

 

If I make a computed column and datepart(date1) then use that in the MDY function: MDY((datepart(date1), 1, (datepartdate1)) then it doesn't work

Super User
Super User
Posts: 7,845

Re: MDY Function

Posted in reply to mmagnuson

You need to give the MDY() values of month and year values, not dates. 

old_dt = '05NOV2017:00:00'dt ;
new_date = mdy(month(datepart(old_dt)),1,year(datepart(old_dt)));
format new_date date9.;

You can also use the INTNX() function to move dates by intervals.

data _null_;
old_dt = '05NOV2017:00:00'dt ;
format old_dt new_dt datetime20. new_date date9.;

new_date = mdy(month(datepart(old_dt)),1,year(datepart(old_dt)));
put (_all_) (=);

new_date=intnx('month',datepart(old_dt),0,'b');
put (_all_) (=);

new_dt = intnx('dtmonth',old_dt,0,'b');
new_date=datepart(new_dt);
put (_all_) (=);

run;
old_dt=05NOV2017:00:00:00 new_dt=. new_date=01NOV2017
old_dt=05NOV2017:00:00:00 new_dt=. new_date=01NOV2017
old_dt=05NOV2017:00:00:00 new_dt=01NOV2017:00:00:00 new_date=01NOV2017
Super User
Posts: 22,823

Re: MDY Function

Posted in reply to mmagnuson

Use INTNX to move the date to the beginning of the month, not MDY.

 

INTNX('month', date1, 0, 'b')
Ask a Question
Discussion stats
  • 9 replies
  • 332 views
  • 0 likes
  • 4 in conversation