6

I'm trying to convert datetime22.3 variable to ddmmmyy10. in proc sql, this is giving me ****** in the output column.

How can I get the correct values in the output column?

Joe
  • 62,789
  • 6
  • 49
  • 67
staq
  • 163
  • 3
  • 3
  • 11
  • can you elaborate more on your problem.Do you want to change date format in SQL. – Ameya Deshpande Oct 13 '14 at 09:46
  • proc sql; create table work.abc as select DISTINCT bd.Account_Id, BD.Billing_Dt /*Datetime22.3 */, from abc table; I want billing_dt to show date ddmmyy10. instead of dateitme. – staq Oct 13 '14 at 09:49
  • use this function it will work `CONVERT(nvarchar(50),getdate(),103)` Instead of `getDate()` put your column name in it. and for different format just change the number at the end of function . i.e. `101,102,103` like that so you will get different formats that you want for more information on datetime format follow this link [datetime](http://www.w3schools.com/sql/func_convert.asp) – Ameya Deshpande Oct 13 '14 at 09:56
  • my slq is as below ---proc sql; create table work.abc as select DISTINCT a.Account_Id, a.Billing_Dt format ddmmyy10. as Bill_date from abc table; I want billing_dt to show date ddmmyy10. instead of dateitme. – staq Oct 13 '14 at 09:57
  • sorry, i was not clear in the beggning, i'm doing this SAS, so its proc sql, functions are different to sql server. thanks for comments. appriciate your effort to answer my question. – staq Oct 13 '14 at 10:02

1 Answers1

16

You need to convert original SAS DATETIME value (think of it as data type) to SAS DATE value using DATEPART() function and apply appropriate format:

proc sql; 
 create table work.abc 
 as select DISTINCT a.Account_Id,
  DATEPART(a.Billing_Dt) format ddmmyy10. as Bill_date
from abc table;
quit;
vasja
  • 4,732
  • 13
  • 15
  • That was on Spot i'm looking for, thanks very much vasja. really appriciate – staq Oct 13 '14 at 10:10
  • Vasja, you might want to post this on the master duplicate as well - somehow it doesn't include the datepart answer as one of the possible ways to do this (among the several other examples). – Joe Oct 13 '14 at 16:25