BookmarkSubscribeRSS Feed
THammers
Calcite | Level 5

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.

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

THammers
Calcite | Level 5

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.

ChrisBrooks
Ammonite | Level 13

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

 

Chris

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 3 replies
  • 1926 views
  • 0 likes
  • 3 in conversation