We have our operational day to day system in Microsoft SQL Server. On a nightly basis (after all of our daily transactions complete), we'd like to have SAS run some statistical analysis and then populate some "reporting tables" in SQL Server with the results. We're thinking the best way to do that (since we don't want SAS to do its thing until the nightly updates are done) is to have SAS invoked by a SQL stored procedure. Basically I was wondering if that is even possible and if so how do I set up SAS to be called remotely like that. Does this make any sense to anyone out there??
You could have your daily transaction update program flag a status table once all updates are completed. New_Data="Yes"
You could schedule your SAS job to run hourly and have it check this table for updates. If "Yes", then run the SAS analysis and reports and at the end update the New_Data table in SQL Server, if "No", do nothing.
You will want ACCESS to SQL Server (or ODBC or OLE DB depending on your setup.)