BookmarkSubscribeRSS Feed
mmagnuson
Quartz | Level 8

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?

9 REPLIES 9
Reeza
Super User

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

 

mmagnuson
Quartz | Level 8

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?

Reeza
Super User

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

 

 

Astounding
PROC Star

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

Reeza
Super User

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)

Tom
Super User Tom
Super User

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.

 

mmagnuson
Quartz | Level 8

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

Tom
Super User Tom
Super User

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
Reeza
Super User

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

 

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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 9 replies
  • 6927 views
  • 0 likes
  • 4 in conversation