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.
what would you like the oracle string to be?
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
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.
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.
@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?
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.