How to use format and formatedlength options in proc casutil load data ?

[yatinrao]

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.

[jimbarbour]

@yatinrao,

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]

Jim

I have tried the first suggestion "(NAME="ROW_DATE",format='ddmonyyyy' formattedlength=9 )" but that just generates a number. I will try the second one today. I do believe almost SAS9.4 code work in Viya also it is likely that this code executes in the SPRE environment.
thanks for your suggestion . SAS Documentation is lacking in this regard.

Yatin

[yatinrao]

Jim

I used format="DATETIME9" FORMATTEDLENGTH=9 which seems to work.

Yatin

[Tom]

---

@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]

Tom

I am only trying to get the DATE part of the field . time part is all zeroes in the original oracle table.

Yatin

[Tom]

---

@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.