英文:
Getting different results due to date filtering method in SAS Proc SQL
问题
我有一个代码块，在这个代码块中，根据日期过滤方式的不同，我会得到不同的结果。
libname orac oracle authdomain= XX path =XX schema = XX;
proc sql;
create table ship_bill_date as (
select distinct(t.shp_pro),min(t.blng_dt) as blng_dt,count(t.shp_pro) as count
from orac.table_A t
where blng_dt between '01-MAY-2023'd and '31-MAY-2023'd
);
quit;
上面的代码给出了预期的200多万行结果。但是，由于我希望日期过滤是动态的，所以我将代码更改为以下内容。
libname orac oracle authdomain= XX path =XX schema = XX;
proc sql;
create table ship_bill_date as (
select distinct(t.shp_pro),min(t.blng_dt) as blng_dt,count(t.shp_pro) as count
from orac.table_A t
where blng_dt between intnx('month', blng_dt, -2, 'b') and intnx('month', blng_dt, -2, 'e')
);
quit;
上面的代码块只返回了770行输出。
我该如何纠正这个问题？
blng_dt列的数据类型是日期。
英文:
I have a code chunk where I get different results based on how I filter for dates.
libname orac oracle authdomain= XX path =XX schema = XX;
proc sql;
create table ship_bill_date as (
select distinct(t.shp_pro),min(t.blng_dt) as blng_dt,count(t.shp_pro) as count
from orac.table_A t
where blng_dt between '01-MAY-2023'd and '31-MAY-2023'd
);
quit;
The above code gives the expected 2+ million rows. However since I want the date filter to be dynamic I changed the code to the following.
libname orac oracle authdomain= XX path =XX schema = XX;
proc sql;
create table ship_bill_date as (
select distinct(t.shp_pro),min(t.blng_dt) as blng_dt,count(t.shp_pro) as count
from orac.table_A t
where blng_dt between intnx('month', blng_dt, -2, 'b') and intnx('month', blng_dt, -2, 'e')
);
quit;
The above code chunk gives only 770 rows of output.
How can I rectify this?
The data type of blng_dt column is date.
答案1
得分: 3
如果你想要获取今天之前2个月的月份，可以使用以下方法。
where blng_dt between intnx('month', today(), -2) and intnx('month', today(), -2, 'e')
如果PROC SQL无法正确优化查询，因为范围是一个常数，可以使用宏代码代替。
where blng_dt between %sysfunc(intnx(month, %sysfunc(today()), -2))
and %sysfunc(intnx(month, %sysfunc(today()), -2, e))
如果你希望%SYSFUNC()生成的值可读性更好，并且生成日期文字而不仅仅是天数，请添加可选的格式规范。
%put
where blng_dt between "%sysfunc(intnx(month, %sysfunc(today()), -2), date11)"d
and "%sysfunc(intnx(month, %sysfunc(today()), -2, e), date11)"d;
这将生成以下日期文字：
where blng_dt between "01-MAY-2023"d and "31-MAY-2023"d
英文:
If you want the month that is 2 months before TODAY() then just do that.
where blng_dt between intnx('month',today(),-2) and intnx('month',today(),-2,'e')
If PROC SQL is too dumb to understand the range is a constant and optimize the query properly then use macro code instead.
where blng_dt between %sysfunc(intnx(month,%sysfunc(today()),-2))
and %sysfunc(intnx(month,%sysfunc(today()),-2,e))
If you want the values that %SYSFUNC() generates to be readable by humans add the optional format specification and generate date literals instead of just literal number of days.
10 %put
11 where blng_dt between "%sysfunc(intnx(month,%sysfunc(today()),-2),date11)"d
12 and "%sysfunc(intnx(month,%sysfunc(today()),-2,e),date11)"d
13 ;
where blng_dt between "01-MAY-2023"d and "31-MAY-2023"d
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论