BookmarkSubscribeRSS Feed
Citrine10
Obsidian | Level 7

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?

8 REPLIES 8
Citrine10
Obsidian | Level 7

already tried that. doesnt work

PeterClemmensen
Tourmaline | Level 20

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;
Citrine10
Obsidian | Level 7
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.
Citrine10
Obsidian | Level 7

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

PeterClemmensen
Tourmaline | Level 20

Show your SQL Code please.

Citrine10
Obsidian | Level 7

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

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

Kurt_Bremser
Super User

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)

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 3237 views
  • 0 likes
  • 3 in conversation