My date columns are showing in this format DDMONYYYY (ex. 08MAR2018) is showing 01JAN1960:06:00:23 in Oralce table.
I have try to add format=Date9. in the select statement to convert the date but it did not help.
Oracle :
Column: DUE_DATE
Date Type: Date
SAS
Column: Due_Date
Date Type: Date9.
My code:
PROC SQL;
INSERT INTO admr.STAR_ACTION_ITEMS
(
NAME_ID,
DUE_DATE
)
Select
Name_ID,
Due_Date FORMAT=Date9.
FROM WORK.ACTION_ITEMS;
QUIT;
How do you format the date in SAS correctly so that it show the actual date in Oracle table?
If you by pass SAS's normal upload method then it can sometimes not realize it needs to convert dates into datetimes to insert into ORACLE. You could convert the value yourself.
INSERT INTO admr.STAR_ACTION_ITEMS (NAME_ID,DUE_DATE)
Select
Name_ID
, dhms(Due_Date,0,0,0)
FROM WORK.ACTION_ITEMS
;
Or try just using more natural SAS code to add the records.
proc append base=admr.STAR_ACTION_ITEMS data=WORK.ACTION_ITEMS;
run;
And see if SAS is smart enough to transform the values for you.
The dhms(Due_Date,0,0,0) worked but the Proc append didn't.
Thank you Tom
Did PROC APPEND still load the number of days as if they were number of seconds?
Or did it not execute? Might need the FORCE option if the two datasets don't have the same variables.
I did use FORCE in the proc append and the date still went in Oracle as 01JAN1960.
Look at this page for how to use dataset options to tell SAS how to convert the variables for you.
http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371624.htm
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.