Issue with data set variable's data type

[rohitguptaecb]

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;

[manojinpec]

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.

[rohitguptaecb]

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;

[manojinpec]

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

[rohitguptaecb]

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