How to filter datetime values between two specific dates using macro variable and PROC SQL

Question

I need to filter my set between two specific dates using PROC SQL.

The variable start_dtm is formatted using PROC FORMAT as 28-09-2018 09:15:51 (format DMYHMS. and informat DATETIME25.6) and original as 28SEP2018:09:15:51.000000 (format and informat DATETIME25.6.)

I want to use a macro variable for the where clause like 01-09-2018.

The code below will not work (except the format) but serves as an example of what I want to do.

PROC FORMAT;
    picture dmyhms
        . = ' ' 
        other = '%0d-%0m-%0Y %0H:%0M:%0S' (datatype=datetime);
RUN;

%LET DATE_MIN = '01-09-2018';
%LET DATE_MAX = '30-09-2018';

PROC SQL OUTOBS=100;
    CREATE TABLE foo AS
       SELECT 
        * 
       FROM MY_SET
       WHERE start_dtm BETWEEN &DATE_MIN AND &DATE_MAX
    ;
QUIT;

How can I best achieve this? Any help much appreciated.

Answer 1

The format only changes how it's displayed to you. Ultimately, it's still a SAS datetime in the end. You have two problems to solve:

1. Your personal interface: how do you want to input your date?
2. Translating the way you input your date into a SAS date for SQL

(2) depends on (1). Since you're using a 'dd-mm-yyyy' format, you'll need to convert that into a SAS date with the ddmmyy10. informat.

We'll make a small modification to your code so that it supports your format by taking the datepart of start_dtm, then converting your min/max macro variable dates into SAS dates.

%LET DATE_MIN = '01-09-2018';
%LET DATE_MAX = '30-09-2018';

PROC SQL OUTOBS=100;
    CREATE TABLE foo AS
       SELECT 
        * 
       FROM MY_SET
       WHERE datepart(start_dtm) BETWEEN input(&DATE_MIN, ddmmyy10.) 
                                     AND input(&DATE_MAX, ddmmyy10.)
    ;
QUIT;