BookmarkSubscribeRSS Feed
tony_mccray
Calcite | Level 5
I'm having a strange problem using SAS/ACCESS and the pass-through utility to connect to an Oracle database. A query that should return data is returning a data set with zero rows. This only seems to happen when the script is running under automation (running on Sun Solaris and executing via cron). When I run the script manually the data returned is as expected. Also, when I copy the query out of the SAS log and execute it I get the correct result. I'm not seeing any errors in the log. When the queries fail this way they also return quickly (1.6 seconds/0.03 seconds compute time versus 31.6 seconds/0.3 seconds compute time when successful). I execute the same script nine times with different parameters, and this issue doesn't occur with each parameter set.

Has anyone else ever experienced a similar issue? If so, did you find a solution?

Thanks,
Tony
4 REPLIES 4
LinusH
Tourmaline | Level 20
You have to find out what is different in the environment between the two scenarios.
If you run via cron, one could suspect that you run this under different accounts. If so, try to see if that can influence your result.
Be sure to use the same autoexec, config, SAS command shell script.
Try to echo as much information as possible into the SAS log, such as PROC OPTIONS, macro variables. If you are using macro processing turn on debugging options.
Are there any environment variables concerning SAS/ACCESS or Oracle behaviouor, check their values.
Since this kind of problems is hard to debug, it might be a good idea to get a ticket from SAS support.

/Linus
Data never sleeps
FriedEgg
SAS Employee
I agree with Linus, running via cron should result in a different system environment. You should check relative system and sas environment from cron vs. running manually on your account. Also you can try to have cron load your user profile prior to executing the script:


0 8-20 * * 1-5 . /home/testuser/.shell_profile your_script_here.sh


Additionally you could task cron to su to a different user and run the script as a given user.
tony_mccray
Calcite | Level 5
Thanks for the replies on this. I think I finally got to the bottom of the problem. One of my macros was getting an error while executing proc gchart:

ERROR: There are no valid observations. Could be caused by SUMVAR= or FREQ= variable.

I had always ignored this message. Since there really weren't any valid observations the fact that I was getting an error and no plot wasn't an issue. During automation, however, sometimes this error would occur, and then each subsequent step would stop running "due to previous errors." So I changed the code so this error won't occur anymore, and the problem appears to be fixed.
SASKiwi
PROC Star
My guess is the NOSYNTAXCHECK SAS option was causing this issue. With this option off then SAS goes into syntax check mode and the OBS option is set to 0 so no code step processes any data. This option is set differently for batch versus interactive SAS sessions by default so you can get different symptoms when an error occurs. Message was edited by: SASKiwi

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 1274 views
  • 0 likes
  • 4 in conversation