I am trying to load data from oracle into SAS . for a date field SAS by default treats it as datetime field.
how do I trim the datetime to date in the load call
my code looks something like this
proc casutil;
load casdata="WSPLIT" casout="WSPLIT"
vars=
(
(NAME="ROW_DATE",format='ddmonyyyy' formattedlength=9 ),
(NAME="ROW_DATE",format='DATE9' formattedlength=9 ),
/*(NAME="ROW_DATE"),*/
(NAME="ABCD"),
)
incaslib="MAIN" outcaslib="DBORA" ;
if I use the commented line row_date shows up like 05OCT2020 00:00:00... the uncommented format
gets me date in 1917388800, I am trying to get just 05OCT2020
SAS Documentation just says
FORMAT="string"
specifies the format to apply to the variable.
I don't have Viya where I work, so let me ask what may be a dumb question: Can you use a function? In more traditional implementations of SAS, I would use the DATEPART function, something like this:
proc casutil;
load casdata="WSPLIT" casout="WSPLIT"
vars=
(
(NAME="ROW_DATE",format='ddmonyyyy' formattedlength=9 ),
(NAME="ROW_DATE",DATEPART(Row_Date),format='DATE9' formattedlength=9 ),
(NAME="ABCD"),
)
incaslib="MAIN" outcaslib="DBORA" ;
If you can't use a function, can you create a view and then use the view in conjunction with the CASUTIL/Load CAS data? Something like:
PROC SQL;
CREATE VIEW My_Lib.My_View AS
SELECT DATEPART(Oracle_Date_Column ) AS Row_Date FORMAT=DATE9.
FROM Some_Lib.Some_Table;
QUIT;
Of course you'd want to add more columns, and you'd want to check my syntax, but if you can create a view, you could create the view with the proper data types for your situation, and then use CASUTIL.
Just throwing out some ideas. Discard if of no help.
Jim
@yatinrao wrote:
Jim
I used format="DATETIME9" FORMATTEDLENGTH=9 which seems to work.
Yatin
There is a big difference between a datetime values (number of seconds) and a date value (number of days) that the DATE format requires. In addition to DATETIME9 there is also the DTDATE9 format for datetime values that displays only the day part.
@yatinrao wrote:
Tom
I am only trying to get the DATE part of the field . time part is all zeroes in the original oracle table.
Yatin
Which is why you had to use a format designed to work with datetime values and not date values. Such as DATETIME, DTDATE, etc.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.