SAS/Access/proc sql Inconsistent Results

[tony_mccray]

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

[LinusH]

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

[FriedEgg]

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]

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]

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