在PROC SQL中使用WHERE语句来过滤一个以datetime20格式化的日期列。

huangapple go评论101阅读模式
英文:

WHERE statement in PROC SQL to filter a date from a column formatted as datetime20

问题

我有一个格式为datetime20的列,并且需要在日期为07JUL2023时获取所有数据,但是我在编写代码时遇到了一些问题。

有人知道如何实现吗?这可能是一个新手问题,但我还在学习中。

提前感谢。

我尝试了我能想到的和在Google上找到的所有方法,比如format、datepart、cast...但是似乎都不起作用。

英文:

I have a column formatted as datetime20. and need all the data whenever this date is 07JUL2023, but I'm having some trouble writing it.

Does anyone know any way to do it? This is probably a newbie question, but I'm still learning

Thanks in advance

I tried everything I could think of and found on google, format, datepart, cast... but nothing seems to work

答案1

得分: 1

首先,请确保你的列是一个 SAS datetime 类型。如果是的话,你可以将其转换为日期并使用日期字面量:'07JUL2023'd

proc sql;
    select *
    from have
    where datepart(datetime) = '07JUL2023'd
    ;
quit;

或者使用 datetime 字面量:'07JUL2023:00:00'dt

proc sql;
    select *
    from have
    where datetime between '07JUL2023:00:00'dt and '07JUL2023:23:59'dt
    ;
quit;

否则,如果它是一个字符串,你需要使用 input 函数将其转换为 SAS datetime 类型。

proc sql;
    select *
    from have
    where datepart(input(datetime, datetime20.)) = '07JUL2023'd
    ;
quit;
英文:

First, make sure your column is a SAS datetime. If it is, you can convert it to a date and use a date literal: '07JUL2023'd

proc sql;
    select *
    from have
    where datepart(datetime) = '07JUL2023'd
    ;
quit;

Or use a datetime literal: '07JUL2023:00:00'dt

proc sql;
    select *
    from have
    where datetime between '07JUL2023:00:00'dt and '07JUL2023:23:59'dt
    ;
quit;

Otherwise, if it's a string, you need to convert it to a SAS datetime with the input function.

proc sql;
    select *
    from have
    where datepart(input(datetime, datetime20.)) = '07JUL2023'd
    ;
quit;

huangapple
  • 本文由 发表于 2023年8月9日 03:19:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/76862629.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定