I have a same issue to convert a numeric(8) to date as YYYY-MM-DDl.
my table has a column, load_dt_id numeric (8)
and in the table load_dt_id is 20161103
when I run procsql:
select input(put(load_dt_id, 10.),YYYYMM10.) as LOAD_DT from table_name;
and my result is still the same as 20161103 instead of 2016-11-03.
how do I convert it from numeric to date, YYYY-MM-DD or to date with time?
thank you.
Bach-Nga
@pepevo wrote:
I have a same issue to convert a numeric(8) to date as YYYY-MM-DDl.
my table has a column, load_dt_id numeric (8)
and in the table load_dt_id is 20161103
when I run procsql:
select input(put(load_dt_id, 10.),YYYYMM10.) as LOAD_DT from table_name;
and my result is still the same as 20161103 instead of 2016-11-03.
how do I convert it from numeric to date, YYYY-MM-DD or to date with time?
thank you.
Bach-Nga
If you want to store hyphens in the value the you will need to create a character variable.
If you want your DATE values to display in that style the use the YYMMDDD10. format. The third D is for DASH. You can leave off the third D as the default for the YYMMDD format is to use a dash when the width is long enough to have room for it.
Note that there is no YYYYMM informat. There is a YYMMDD informat and a YYMM informat (which will set the date value to the first of the month).
So if you have numeric values in YY,YYM,MDD format then you can convert them to DATE values and attach a format to have them displayed as YYYY-MM-DD values using syntax like this in SQL.
input(put(load_dt_id, 8.),YYMMDD8.) as LOAD_DT format=yymmdd10.
If you want to include a time component then you need to use DATETIME variable instead of DATE variable. Dates are stored as number of days. DATETIME is stored as number of seconds. You can use DATEPART() and TIMEPART() to extract date and time values from datetime values. You can use DHMS() function to construct datetime values from the components (D=Days, H=Hours, M=Minutes, S=Seconds). So if you have DATE and a TIME value you can construct a DATETIME value using:
dhms(date,0,0,time)
I did try that but didn't put "dd" in it.
select input(put(load_dt_id, 10.),YYYYMM10.) as LOAD_DT format=yymm10. from table_name;
Thank you for correcting my syntax so much.
v/r,
Bach_Nga
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.