BookmarkSubscribeRSS Feed
Citrine10
Obsidian | Level 7

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;
12 REPLIES 12
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Kurt_Bremser
Super User

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
Obsidian | Level 7
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
Super User

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
Obsidian | Level 7

This is how getDate is created:


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

 

Kurt_Bremser
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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. today1.PNG

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 1021 views
  • 1 like
  • 4 in conversation