topic Re: How to use format and formatedlength options in proc casutil load data ? in SAS Programming

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

yatinrao — Fri, 23 Oct 2020 19:03:37 GMT

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.

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

jimbarbour — Fri, 23 Oct 2020 23:08:03 GMT

@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

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

yatinrao — Mon, 26 Oct 2020 13:15:26 GMT

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

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

yatinrao — Mon, 26 Oct 2020 15:39:27 GMT

Jim

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

Yatin

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

Tom — Mon, 26 Oct 2020 16:11:59 GMT

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

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

yatinrao — Mon, 26 Oct 2020 17:58:01 GMT

Tom

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

Yatin

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

Tom — Mon, 26 Oct 2020 18:19:51 GMT

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