Desktop productivity for business analysts and programmers

MDY converts datetime variable issue

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

MDY converts datetime variable issue

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.

PlayerDayDateConvert Result
01Jul2015:00:00:00.0001Jul2015

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


Accepted Solutions
Solution
‎07-02-2015 04:28 PM
Respected Advisor
Posts: 4,130

Re: MDY converts datetime variable issue

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

View solution in original post


All Replies
Super User
Posts: 3,233

Re: MDY converts datetime variable issue

Gong from datetime to date only requires DATEPART:

datepart(t1.PlayerDayDate)

Frequent Contributor
Posts: 104

Re: MDY converts datetime variable issue

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

Super User
Posts: 3,233

Re: MDY converts datetime variable issue

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

Frequent Contributor
Posts: 104

Re: MDY converts datetime variable issue

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

Solution
‎07-02-2015 04:28 PM
Respected Advisor
Posts: 4,130

Re: MDY converts datetime variable issue

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

Frequent Contributor
Posts: 104

Re: MDY converts datetime variable issue

Thank you for your help.  It worked.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 562 views
  • 0 likes
  • 3 in conversation