51工具盒子英文:
How to filter datetime values between two specific dates using macro variable and PROC SQL
问题 {#heading}
我需要使用PROC SQL在两个特定日期之间过滤我的数据集。
变量 start_dtm 使用PROC FORMAT格式化为 28-09-2018 09:15:51（格式为DMYHMS，格式输入为DATETIME25.6），原始格式为 28SEP2018:09:15:51.000000（格式和输入格式为DATETIME25.6）。
我想要在where子句中使用一个宏变量，例如 01-09-2018。
下面的代码除了格式之外不会起作用，但作为我想要做的示例。
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;
我如何最好地实现这个？非常感谢任何帮助。 英文:
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.
答案1 {#1}
得分: 2
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:
- Your personal interface: how do you want to input your date?
- 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;
英文:
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:
- Your personal interface: how do you want to input your date?
- 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;
`
答案2 {#2}
得分: 2
如果您可以控制宏变量的值，最简单的方法是提供一个SAS日期时间字面值，而不是文本字符串。使用日期时间字面值，SAS知道它是一个日期时间值，并可以适当地使用它。所以您可以编写以下代码：
%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;
这假定您的变量START_DTM是一个日期时间，而不是一个日期。如果它是一个日期，您将使用日期字面值：
%LET DATE_MIN = '01Sep2018'd;
%LET DATE_MAX = '30Sep2018'd;
英文:
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;
