Extracting date part from Timestamp

[hexx18]

Hi ,

I have a a dataset dates which has trandt and latest id has date 19Mar2022 and then oracle table 

Spe.Transaction has tran_date which is a timestamp 19Mar2022:00:00:00 how should i match trandt and tran_date in where clause.

DATA dates;
INPUT id 1-4 trandt date9.;
CARDS;
1004 26Dec2021
1005 03Jan2022
1006 03Feb2022
1007 19Mar2022
;
RUN;
PROC PRINT DATA=dates;
format trandt date9.;
RUN;

%Macro get_tran();
%global trandt;
Data _null_;
set dates end=eof;
by id;
if last.id;
if eof then call symput('trandt', trandt);
run;
%Mend;

%get_tran;
%put trandt = &trandt.;

Trandate in oracle is like below timestamp

tran_DATE
19Mar2022:00:00:00

Proc SQL outobs=10;
Connect to Oracle(user="" password="" path="");
Create table test50 as select * from connection to oracle
(Select distinct t1.Tran_Nm,
t1.Tran_KEY,
t1.Time
from Spe.Transaction t1
where TO_CHAR('t1.tran_DATE', 'DDMONYYYY') = "&trandt."
)
;
Disconnect from Oracle;
Quit;

Can anyone please help ? 

[PaigeMiller]

I'm not sure how ORACLE impacts this, but if both tables were SAS data sets, you would want

where datepart(tran_date)=trandt

Whatever you do, DO NOT work with these values a character strings, as I think your code is doing. 

[hexx18]

Hi ,
Yes datepart in SAS in oracle it’s little different

Thank you

[SASKiwi · Accepted solution]

If you are using this: where TO_CHAR('t1.tran_DATE', 'DDMONYYYY') = "&trandt." to convert the datetime into a date string then &trandt should be just the character string 25FEB2023 for example.

[hexx18]

Thank you . I will try this and update .

[Patrick]

You haven't shared with us if your current code returns an error or just not the desired result.

I can't test it but believe below should work.

Just from memory a few things you need to pay attention to:

- In Oracle strings need to be within single quotes. SAS macro %tslit() allows to wrap single quotes around a macro variable that you first need to resolve.

- Ensure that the casing of the two text strings match. I believe the way I've done it below both strings will be all upper case.

- Ensure that your SAS session and your Oracle session both run in the same time zone. If not then you need to investigate how you can ensure that the dates "match" - which given you only have a date portion in Oracle and SAS might create some difficulties. If you also had a time portion other than beginning of the day in Oracle then you would need to shift the timestamp value to match the SAS session timezone prior to casting it to a string.

data dates;
  input id 1-4 trandt :date9.;
  cards;
1004 26dec2021
1005 03jan2022
1006 03feb2022
1007 19mar2022
;

%let trandt=;
proc sql noprint;
  select put(trandt,date9. -l) into :trandt trimmed
  from dates
  having max(id)=id
  ;
quit;

proc sql outobs=10;
  connect to oracle(user="" password="" path="");
  create table test50 as select * from connection to oracle
    (
      select distinct t1.tran_nm,
        t1.tran_key,
        t1.time
      from spe.transaction t1
      where to_char('t1.tran_date', 'DDMONYYYY') = %tslit(&trandt.)
    )
  ;
  disconnect from oracle;
quit;

[hexx18]

I will also try this and update . Thank you .