Convert date type in do loop?

Question

I am trying to write a SAS macro, in which I need to loop a date range and use the date in the where condition in PROC SQL step. At the same time, I have to use a path through in the PROC SQL step.

The start_date field in table1 is in the datetime format, i.e. '15mar2021:00:00:00'.

I tried to convert the date format for &i in the where clause to match the format of start_date field in table1. I tried below query but got this error: ERROR: CLI describe error: [Oracle][ODBC][Ora]ORA-00923: FROM keyword not found where expected

%macro date_delq(start,end);
%let start=%sysfunc(inputn(&start,anydtdte9.));
%let end=%sysfunc(inputn(&end,anydtdte9.));
%do i= &start %to &end;
  proc sql;
     connect using admin as server;
          create table delq_&i as
                select * from connection to server(
     select *
     from table1
     where start_date= CONVERT(datetime,&i,106)
);
run;
%end;
%mend date_delq;
%date_delq(01mar2021,02mar2021);

Thank you for your help!

CONVERT(datetime,&i,106) looks like SQL Server syntax to me. If startdate is a DATE column as it should be (DATE is Oracle's inappropriate name for datetime), then it should suffice to select * from table1 where start_date = &i. If despite its name the start_date has a time part you want to get rid of, you can truncate it to date only by applying TRUNC: where trunc(start_date) = &i. — Thorsten Kettner
If your variable &i is a string instead of a date or datetime, then convert this with TO_DATE. Make sure to specify the format used in &i, e.g. to_date(&i, 'yyyy-mm-dd') if &i is a string containing a four -digit year, a dash, a two-digt month, a dash, and a two-digit day. — Thorsten Kettner

Littlefoot Littlefoot · Accepted Answer · 2021-03-24T07:17:40

I don't use SAS.

However, as it looks that you use an Oracle database, I presume that start_date column's datatype is date (as it should be; if it is varchar2, table designer has made a HUGE mistake).

In Oracle, dates are stored in its internal format, unreadable to us, humans. That's why we use functions (like TO_CHAR) or alter session to set NLS_DATE_FORMAT or apply desired format mask in our front-end applications to display those date datatype values as we want.

Therefore, there's no use in "converting" column contents from e.g. 15Mar2021 00:00:00 to 21mar2021 because you won't achieve anything, data will still be stored in the same internal format and - unless you do something about presenting those values, you won't see any difference.

What you could / should do is to use desired format mask, e.g.

SQL> with test (start_date) as
  2    (select to_date('24.03.2021 08:14:23', 'dd.mm.yyyy hh24:mi:ss') from dual)
  3  select to_char(start_date, 'fmdd Month yyyy, hh24:mi') example_1,
  4         to_char(start_date, 'Mon dd, yyyy') example_2,
  5         to_char(start_date, 'hh24:mi:ss') example_3
  6  from test;

EXAMPLE_1           EXAMPLE_2    EXAMPLE_
------------------- ------------ --------
24 March 2021, 8:14 Mar 24, 2021 08:14:23

SQL>

or

SQL> alter session set nls_date_format = 'dd Mon yyyy, hh24:mi';

Session altered.

SQL> select sysdate from dual;

SYSDATE
------------------
24 Mar 2021, 08:16

SQL>

and so forth.

Convert date type in do loop?

1 Answers