convert datetime to date in proc-sql sas

sas proc-sql
staq picture staq · Oct 13, 2014 · Viewed 51.1k times · Source

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?

Answer

vasja picture vasja · Oct 13, 2014

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;

Related questions

Limiting results in PROC SQL

I am trying to use PROC SQL to query a DB2 table with hundreds of millions of records. During the development stage, I want to run my query on an arbitrarily small subset of those records (say, 1000). I've tried using …

Read More
How to convert date in SAS to YYYYMMDD number format

In test_1 table, the my_date field is a "DATE9." format. I would like to convert it to a pure numeric format (number length 8) which is of the form YYYYMMDD. I would also like to do this in a proc …

Read More
LEFT JOIN in SAS using PROC SQL

I am new to SAS and have this basic problem. I have a list of NYSE trading dates in table A as follows - trading_date 1st March 2012 2nd March 2012 3rd March 2012 4th March 2012 5th March 2012 6th March 2012 I have …

Read More