topic Re: Yesterdays date in SAS Data Management

Yesterdays date

Citrine10 — Mon, 25 Jan 2021 07:57:31 GMT

hi there

how to I define in my where clause to get yesterdays date?

For example in SQL it would be 'where DateBought = (getdate() - 1)'

what would be the getdate() equivalent in SAS?

Re: Yesterdays date

PeterClemmensen — Mon, 25 Jan 2021 07:59:04 GMT

The Today Function.

Re: Yesterdays date

Citrine10 — Mon, 25 Jan 2021 08:02:26 GMT

already tried that. doesnt work

Re: Yesterdays date

PeterClemmensen — Mon, 25 Jan 2021 08:05:56 GMT

Please elaborate on 'doesn't work'?

This gives you todays and yesterdays date.

data _null_;
   today = today();
   yesterday = today() - 1;
   put today= date9. yesterday= date9.;
run;

Re: Yesterdays date

Citrine10 — Mon, 25 Jan 2021 08:22:14 GMT

ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]'today' is not a recognized built-in
function name. : [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of
compile errors.

Re: Yesterdays date

Citrine10 — Mon, 25 Jan 2021 08:27:48 GMT

I'm trying to do it in a proc sql;

Re: Yesterdays date

PeterClemmensen — Mon, 25 Jan 2021 08:40:07 GMT

Show your SQL Code please.

Re: Yesterdays date

Citrine10 — Mon, 25 Jan 2021 08:53:02 GMT

%LET START = TODAY(SYSDATE,'DD-MON-RR') -1;

proc sql;
create table Unsigned as
select *
from connection to odbc (
select
*
where
(DateDrafted = &START)
);
run;

Re: Yesterdays date

Kurt_Bremser — Mon, 25 Jan 2021 10:21:06 GMT

So you are doing an explicit pass-through to MS SQL Server via ODBC.

This means that you need to use the SQL Server functions (and math) in the pass-through.

See here

(first result of a Google search for "ms sql server get yesterday's date", see Maxim 6)