MDY converts datetime variable issue

[sdang]

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

[SASKiwi]

Gong from datetime to date only requires DATEPART:

datepart(t1.PlayerDayDate)

[sdang]

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

[SASKiwi]

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

[sdang]

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

[Patrick · Accepted solution]

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

[sdang]

Thank you for your help.  It worked.