topic Issue with data set variable's data type in SAS Procedures

Issue with data set variable's data type

rohitguptaecb — Fri, 08 Mar 2013 09:43:24 GMT

I am using an etl code written in sas. It takes two manual dates from user into macro variables and stores their values in date9. format in sas table.

Below is the table description.Same table suppose to be created in oracle. Suppose if values in variables START_DATE AND END_DATE is passed 01APR2012 and 03MAR2013 resp and when it moves to oracle, then the data type of both variables START_DATE AND END_DATE in table" LOAD_MASTER_TABLE" automatically changes to datetime20. which are giving wrong values as 01JAN1960 00:00:00 . Is there any way by which the values of these two variables remains as it should be .

DATA LOAD_MASTER_TABLE;

INPUT START_DATE DATE9. END_DATE DATE9. LOAD_RUN_TYPE $10.

LOAD_JOBID $19. STATUS $ 5. LOAD_TYPE $ 2. LOADRUN_DATE DATETIME20.;

FORMAT START_DATE DATE9. END_DATE DATE9. LOAD_RUN_TYPE $10.

LOAD_JOBID $19. STATUS $ 5. LOAD_TYPE $ 2. LOADRUN_DATE DATETIME20.;

DATALINES;

RUN;

LIBNAME RnD ORACLE USER= .... PASSWORD= .... PATH= ..... ;

PROC SQL NOPRINT;

INSERT INTO RnD.LOAD_MASTER_TABLE

SET START_DATE = &START_DATE.,

END_DATE = &END_DATE.,

LOAD_RUN_TYPE = "&LOAD_TYPE.",

LOADRUN_DATE = DATETIME(),

LOAD_JOBID= "&LOAD_PK",

LOAD_TYPE = 'F';

QUIT;

Re: Issue with data set variable's data type

manojinpec — Fri, 08 Mar 2013 09:57:30 GMT

Hi Rohit,

The SAS is passing numeric date value.However oravle interpret it as number of seconds after 01jan1960. Try passing datetime variable for 01APR2012 00:00:00.

Re: Issue with data set variable's data type

rohitguptaecb — Fri, 08 Mar 2013 09:58:54 GMT

Plz answer some one. I need the resolution. i am new on this community

Re: Issue with data set variable's data type

rohitguptaecb — Fri, 08 Mar 2013 10:02:19 GMT

Thank u sir for your reply.

I am using this code to give dates to macro variables. These are manually entered by user . How can i add time value also to these variables ?

%LET START_DATE = 01APR2012;

%LET END_DATE = 03MAR2013;

Re: Issue with data set variable's data type

manojinpec — Fri, 08 Mar 2013 10:16:10 GMT

Try concatenating it with 00:00:00 and give the variable format at datetime . I don't have any oracle backend otherwise would have tried and then sent the code