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

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.

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;

If you have control over the value of the macro variables, the easiest thing to do would be to provide a SAS date-time literal value, instead of a text string. With a date-time literal, SAS knows it is a date-time value, and can use it appropriately. So you could code:

%LET DATE_MIN = '01Sep2018:00:00'dt;
%LET DATE_MAX = '30Sep2018:23:59'dt;

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

That assumes your variable START_DTM is a date-time, not a date. If it's a date, you would use a date literal:

%LET DATE_MIN = '01Sep2018'd;
%LET DATE_MAX = '30Sep2018'd;