In order to take advantage of an Oracle server's vastly larger disk space and RAM, is it possible to run a SAS procedure (eg, proc glimmix or proc nlmixed) on a dataset stored on a server using the ODBC interface?
Or am I limited to extracting datasets to my PC via ODBC and not actually manipulating or analyzing the data with SAS while the data resides on the server?
At the end of the day, some of the work will have to be done by SAS on your PC, assuming you're doing anything complicated (like GLIMMIX would be). SAS (in particular 9.3 or newer) is pretty smart about making the database do as much work as possible; for example, even some PROC MEANS may execute fully on the database side.
However, this is only true to the extent that the procedure can be translated into database functionality without extraordinary measures. SAS isn't likely to perform a regression on the database side, since that's not native Oracle. The data has to make its way across the (likely limited) bandwidth, to some extent.
You can certainly do a lot to limit what you have to do in SAS. Any presummarization can be done in Oracle; any other data prep work prior to the actual PROC GLIMMIX can likely be done in Oracle. You can certainly give it a shot by simply using libname connections and doing something like
proc glimmix data=oracle.table ... options ... ;
run;
and seeing what happens - maybe it'll surprise you, or even me, in how much it handles in-database. It might bring it over locally, it might not.
You may want to consider asking a question with a simplified version of what you're doing, including example data, and simply asking if anyone has any ideas for improving performance. There's a lot of tweaking that can be done, and perhaps some of us here can help.
