topic Re: MDY Function in SAS Enterprise Guide

MDY Function

mmagnuson — Wed, 15 Nov 2017 20:57:19 GMT

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?

Re: MDY Function

Reeza — Wed, 15 Nov 2017 21:09:57 GMT

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-functions-do-not-work/m-p/413793

Re: MDY Function

mmagnuson — Wed, 15 Nov 2017 21:13:23 GMT

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?

Re: MDY Function

Astounding — Wed, 15 Nov 2017 21:20:24 GMT

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.;

Re: MDY Function

Reeza — Wed, 15 Nov 2017 21:23:47 GMT

@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).

Re: MDY Function

Reeza — Wed, 15 Nov 2017 21:53:24 GMT

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)

Re: MDY Function

Tom — Wed, 15 Nov 2017 22:21:10 GMT

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.

Re: MDY Function

mmagnuson — Thu, 16 Nov 2017 13:57:17 GMT

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

Re: MDY Function

Tom — Thu, 16 Nov 2017 15:06:51 GMT

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

Re: MDY Function

Reeza — Thu, 16 Nov 2017 15:50:58 GMT

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

INTNX('month', date1, 0, 'b')