topic Re: MDY converts datetime variable issue in SAS Enterprise Guide

MDY converts datetime variable issue

sdang — Thu, 02 Jul 2015 19:19:56 GMT

Hi all,

I couldn't figure this out. I need all your help. I have a datetime variable -PlayerDayDate-- and I want to convert to look like this "01/Jun/15" so I can join a import file with the date formatted as such "01/Jun/15".

Below is the image of the datetime variable. Below that image is the function I used in Query builder.

PlayerDayDate	Convert Result
01Jul2015:00:00:00.00	01Jul2015

MDY(datepart(t1.PlayerDayDate),datepart(t1.PlayerDayDate),datepart(t1.PlayerDayDate))

The function above is valid but all I get are blanks rows. What I have done wrong?

Thanks

Re: MDY converts datetime variable issue

SASKiwi — Thu, 02 Jul 2015 19:32:19 GMT

Gong from datetime to date only requires DATEPART:

datepart(t1.PlayerDayDate)

Re: MDY converts datetime variable issue

sdang — Thu, 02 Jul 2015 19:36:27 GMT

Thank you for the reply. I tried that but when I import a file with dates as "01Jul15" I cannot join the two columns together. I think SAS still reads datepart(T1.PlayerDayDate) as 20269 eventhough I formatted as "01Jul15".

Thanks

Re: MDY converts datetime variable issue

SASKiwi — Thu, 02 Jul 2015 20:03:19 GMT

When you apply datepart then you also need to assign a format to the result like date9 so SAS correctly displays it as a date. I don't know what you mean by "I cannot join the two columns together".

Re: MDY converts datetime variable issue

sdang — Thu, 02 Jul 2015 20:08:22 GMT

I want to join datepart(PlayerDayDate) with an imported date column which is in this formatted: 01Jul15. When I join PlayerDayDate with that imported column, I get blanks. SAS reads the imported column as 01Jul15 and Datepart(PlayerDayDate) as its numeric value (ie. 20269) not as the formatted date9.

Thanks

Re: MDY converts datetime variable issue

Patrick — Thu, 02 Jul 2015 20:28:56 GMT

Formats are for display (printing) only. For processing like a comparison/join/... SAS uses the internal values (so: 20269). The format applied is irrelevant.

I believe your data is not matching because 20269 represents a date of 30JUN2015 and that's not the same like 01Jul15.

data _null_;

dt=20269;

put "Date is: " dt date7.;

stop;

run;

Date is: 30JUN15

Re: MDY converts datetime variable issue

sdang — Thu, 02 Jul 2015 20:31:24 GMT

Thank you for your help. It worked.