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;
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.
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;
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
Plz answer some one. I need the resolution. i am new on this community
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.