topic Re: How to handle date in sas while using passthrough from oracle in SAS Programming

How to handle date in sas while using passthrough from oracle

anirudhs — Thu, 07 Apr 2022 08:25:41 GMT

Hi how to handle the below date format in sas

07APR2019:21:28:58.00865

as fetching this data from an oracle table using a pass-through code. Where the date variable New_date is used as filter

e.g New_date between to_date(&start_dt, "DDMONYYYY:HR24:MI:SS") and to_date(&end_dt, "DDMONYYYY:HR24:MI:SS")

If you closely look to date the data post decimal is creating fetching issue.

Re: How to handle date in sas while using passthrough from oracle

ChrisNZ — Thu, 07 Apr 2022 22:12:28 GMT

what would you like the oracle string to be?

Re: How to handle date in sas while using passthrough from oracle

ChrisNZ — Thu, 07 Apr 2022 22:24:30 GMT

If you just want to change the macro string, you can do that. For example:

%let a=07APR2019:21:28:58.01865;

%put b=%scan(&a,1,.);

%put c=%sysfunc(prxchange(s/(.*\.\d\d).*/\1/,1,&a));

b=07APR2019:21:28:58
c=07APR2019:21:28:58.01

You could even just use %substr() since the length is aways known

Re: How to handle date in sas while using passthrough from oracle

Tom — Fri, 08 Apr 2022 03:52:07 GMT

I don't understand what code you are trying to run.

What does the MACRO variable referenced in you ORACLE statement contain? Is it the name of an variable in the Oracle query? Something else?

Show the ORACLE code that works.

Explain what part of the ORACLE code you want to generate using SAS macro code.

Re: How to handle date in sas while using passthrough from oracle

anirudhs — Fri, 08 Apr 2022 06:50:43 GMT

In oracle table ABC the variable New_date contains 07APR2019:21:28:58.00865 this data.
when i am using passthrough in sas extracting data from ABC extracting the data between two dates so using WHERE NEW_DATE between to_date(&start_dt, "DDMONYYYY:HR24:MI:SS") and to_date(&end_dt, "DDMONYYYY:HR24:MI:SS") but when the code is executing it is running for infinite time. and the required data is 07APR2019:21:28:58 or only date 07APR2019

Re: How to handle date in sas while using passthrough from oracle

anirudhs — Fri, 08 Apr 2022 06:52:36 GMT

the macro contains the time :hh:mm:ss .. when the code goes for scanning it gets 07APR2019:21:28:58.00865 in data so the code is keeping on executing.

Re: How to handle date in sas while using passthrough from oracle

anirudhs — Fri, 08 Apr 2022 06:55:13 GMT

the macro variable
auth_st_dt = intnx('Month',today(), -1,'b');
auth_end_dt= intnx('Month',today(), -1,'e');
call symput ('auth_st_dt' , "'" || put(auth_st_dt, date9.) || ":00:00:00'");
call symput ('auth_end_dt', "'" || put(auth_end_dt, date9.) || ":23:59:59'");

Re: How to handle date in sas while using passthrough from oracle

SASKiwi — Fri, 08 Apr 2022 08:02:23 GMT

Please post the complete SAS log of your Oracle query so we can fully understand what you are doing. Just posting bits of what you are doing isn't useful.

Re: How to handle date in sas while using passthrough from oracle

Tom — Fri, 08 Apr 2022 12:29:57 GMT

@anirudhs wrote:
the macro variable
auth_st_dt = intnx('Month',today(), -1,'b');
auth_end_dt= intnx('Month',today(), -1,'e');
call symput ('auth_st_dt' , "'" || put(auth_st_dt, date9.) || ":00:00:00'");
call symput ('auth_end_dt', "'" || put(auth_end_dt, date9.) || ":23:59:59'");

So you are creating macro variables that have in them strings in the style generated by the DATETIME format that are enclosed in single quotes.

%let auth_st_dt = '08APR2022:00:00:00' ;
%let auth_end_dt = '30APR2022:23:59:59';

Then in the ORACLE code you inserting that string. So to ORACLE that looks like a string constant.

Is the field in ORACLE a DATE or a TIMESTAMP?