yesterday function not working

[Citrine10]

Hi there

I am trying to get yesterdays records from a dataset. It works perfectly in SAS however after creating a job on SAS management console, I see it did not produce any records.

When I run it in SAS it produces 4000 records, however when it runs on SAS management console as a process, it produces 0 records. 

proc sql;
create table yestrdayrecords as 
select * from getDate
where date1 = intnx('day',today(),-1)
;quit;

[Quentin]

First, carefully check the log from your job and make sure there are no bad notes/warnings/errors before this in the log.  If SAS hits an error in a job, it can sent system option obs=0 which will result in 0 obs being processed.  There will be a note about this in the log.

You may also want to check that the system date on the server running the job is set correctly, by adding code like:

data _null_ ;
  x=today() ;
  put x date9. ;
run ;

Before the query.

[Kurt_Bremser]

The source dataset is a temporary dataset in WORK, so it is local to the process. Work through the log to see where and how it is created, it's probably empty.

[Citrine10]

Correct, it is a local dataset created in the work library. Is this why it is storing zero records? How do I prevent this?

[Kurt_Bremser]

Inspect the log to see where and how

getDate

is created. Then work back from there (if needed) until you find where the batch process behaves differently than your interactive submit.

[Citrine10]

This is how getDate is created:

data getDate;
set mydata.sampleset;
date1 = datepart(loaddate);
format date1 date9.;
run;

[Kurt_Bremser]

Don't post the code. Post the log by copy/pasting the whole text (code and messages) of the step into a code box(</>)..

For comparison, also post the log from your successful manual execution.

[Citrine10]

hi, so I see SAS generates macro date variables and the one I need to use is TODAY1_DATE9. I have converted my variable date1 from date to string to see if that will work when I do the where clause however it is still not working:

data getDate;
set mydata.sampleset;
date1 = datepart(loaddate);
format date1 date9.;
char_date = put(date1,date9.);
run;

%let mydate = &TODAY1_DATE9.;

proc sql;
create table oldrecs as 
select * from getDate
where char_date = &TODAY1_DATE9.;
;quit;

Log:

29         proc sql;
30         create table oldrecs as
31         select * from getDate
32         where char_date = &TODAY1_DATE9.;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "TODAY1_DATE9".
32          27MAR2023
              _______
              22
              76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET, 
              GROUP, GT, GTT, HAVING, LE, LET, LT, LTT, NE, NET, OR, ORDER, ^=, |, ||, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

33         ;quit;

Any idea on how to resolve this by using the date macro variables? 

[Kurt_Bremser]

Please show the code where the macro variable (today1) is created.

The way you use it, it should contain the raw, unformatted date value (count of days since 1960-01-01). See Maxim 28: Macro Variables Need No Formats.

[Citrine10]

I do not create today1. It is automatically created when running any sas program. it is the built in sas macro variables that gets generated. 

[Kurt_Bremser]

There should be (from the systematics) a variable

TODAY1_SASDATE

in this list. Use this in your program.

[Quentin]

I'm surprised to see that long list of TodayXX macro variables.  Just curious, in that screenshot, what program / client are you showing? Is that from SAS Studio, Enterprise Guide, or SAS Management Console? If you scroll down, how many TodayXX variables do you have?

I'm hoping these macro variables were generated by some custom program / autoexec that runs on your server.  I've seen SAS clients clutter the symbol table with global macro variables. But I'd hate to think that some developer in Cary thought it was a good idea to make hundreds of macro variables to store the current date (and prior dates) in various formats.

[Tom]

From that log the value of the macro variable TODAY1_DATE9 is the string 27MAR2023.

So your code generates this boolean expression in the WHERE condition:

char_date = 27MAR2023

Which is not valid SAS code since the thing after the equal sign is not a literal value or a variable reference or and expression.   

If CHAR_DATE is a character variable then you need to generate code like 

char_date = "27MAR2023"

instead.  So just add the quotes around the reference to the macro variable in your code:

char_date = "&TODAY1_DATE9."

If you wanted to compare it to an actual date value you would also need to append the letter D.

date1 = "&TODAY1_DATE9."d