11-09-2016 04:52 PM
I'm trying to set up an Excel model that has a number of user inputs. Based on these inputs I would use VBA to create some SQL code that would then get passed to SAS and the resulting output pulled back to Excel using ActiveX Data Objects.
I'm asking if anyone has some example code that would establish the connection assuming I am working at an office with SAS and data stored in servers external to my local workspace. I know this is as general as it gets but I've done it before I just can't recall the exact VBA code to use. I know I need the servername and the port but beyond that I'm not sure. Any help would be appreciated.
I'm on SAS 9.4 and Excel 2016. I'm trying to avoid any Excel Add-Ins that would require user input.
11-10-2016 04:25 AM
Errm, so your using Excel as your datase, then via VBA are generating some SQL, which you pass through to SAS to create something, which then passes back to Excel? That sounds a bit over complex. Can you not agree on one technology, for example:
Use SAS to do things then export a final report to Excel.
Import all data to a database, then either export or pull from to Excel.
Whilst it may be possible, your reliant on various different technologies and interdependancies with that method.
11-10-2016 08:42 AM
The issue is that my business users are not SAS proficient and don't have time to learn it. Generating the SQL to create the dataset using VBA is simple. And your solutions do not rely on one technology either. My solution only requires a user to open the excel file, select some drop-downs and click a button. I've done it before it's just a matter of setting up the connection.
I appreciate the response.
11-13-2016 04:40 AM
Take a look at my paper from SAS Global Forum 2014 where I do essentially what I think you want http://support.sas.com/resources/papers/proceedings14/1598-2014.pdf