BookmarkSubscribeRSS Feed
omerzeybek
Obsidian | Level 7

Hi i have a simple question, but i am unable to solve it

i just want to automize one of my queries and i need to change WHERE clause to System date -1 day. Is it possible in EG 5.1 using SYSDATE-1 instead of proper date

PROC SQL;

17            CREATE TABLE WORK.SAMPLE_QUERY AS

18            SELECT t1.ID_NO,

19                   t1.FIRST_DATE

20               FROM O_C_OPR.DATABASEONE t1

21               WHERE t1.KREDI_TARIHI = '1Nov2013:3:0:0'dt;

Thank you E

5 REPLIES 5
art297
Opal | Level 21

Do you want beginning of month, day before, or one second before the system datatime?

omerzeybek
Obsidian | Level 7

of course. sorry for unspecific explanation

i need to find one day prior to last updated system date

Patrick
Opal | Level 21

All of Art's questions plus: Does this query run against a table in a database?

You probably wouldn't use &sysdate as this is the date when you started your SAS EG session. I would assume the solution will be something using datetime() together with intnx().

If the source table is in a database then you could create a datetime string so that the query gets pushed to the data base. Something like: "&datestring"d

Assuming that you want to run the job overnight there is always midnight to be considered. So can you tell us between which times the job could possibly run and then which datetime value you want to pass to the where clause?

omerzeybek
Obsidian | Level 7

Yes it is working  against a table in the database

i just need yo run program every morning  at 09:00 AM

Patrick
Opal | Level 21

Something like below should do the job. It's using the SAS datetime() function to retrieve the current system datetime and it's using intnx() to shift this datetime value to the beginning of the previous day.

I'm using %sysfunc() to create a string containing the required datetime value so I then can used 'dt' to convert it to a datetime value. This way the SAS Access engine "knows" that it is dealing with a datetime value and will convert it to an appropriate data base SQL expression.

There might be easier way of doing this - but below is what I'm normally doing and where I know that it will work.

I've also added "options sastrace=",,,d" sastraceloc=saslog nostsuffix;"

Once you run the code against a data base these options will show you in the log what part of the SQL has been sent to the data base for processing. If the SAS Access engine wasn't able to translate everything then you might consider to re-formulate your SQL or to use explicit pass-through SQL.

data have;
  format dt dt_string_to_dt datetime21.;
  dt_string_to_dt="%sysfunc(intnx(dtday,%sysfunc(datetime()),-1,b),datetime21.)"dt;
  do i=-5 to 5;
    dt=intnx('dtday',datetime(),i,'b');
    output;
  end;
run;

options sastrace=",,,d" sastraceloc=saslog nostsuffix;
proc sql;
  create table want as
  select *
  from have
  where dt>="%sysfunc(intnx(dtday,%sysfunc(datetime()),-1,b),datetime21.)"dt
  ;
quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3024 views
  • 1 like
  • 3 in conversation