topic Re: I have a question about the sas add in for excel in Developers

I have a question about the sas add in for excel

Wentzwagon11 — Tue, 26 Mar 2019 14:25:20 GMT

Hello,

My boss wants me to create an excel file that would contain a list of ID's that could be updated and then once the ID's are set have the ability to hit a button that would fire off VBA code that would read in the ID's and execute the SAS Add in Query that would join information to the ID numbers. Lastly it would need to export the information to the same excel file. I am experienced in SAS, but not so much with VBA and stored processes. Any guidance would be appreciated.

Thanks

Re: I have a question about the sas add in for excel

Rajesh3 — Wed, 27 Mar 2019 10:37:58 GMT

Hi,

If you have an add-in for SAS in excel, I think you just need minimal VBA knowledge to accomplish your task. You can execute SAS code via VBA. Once you have the developer tab activated in the excel toolbar, you can create customize a sheet as you like and add a button. However, if you want SAS to export to the same excel file at the end, you may need to close your file first so that SAS can use it.

Thank you,

Rajesh.

Re: I have a question about the sas add in for excel

DWilson — Thu, 28 Mar 2019 15:33:22 GMT

@Wentzwagon11 wrote:
Hello,

My boss wants me to create an excel file that would contain a list of ID's that could be updated and then once the ID's are set have the ability to hit a button that would fire off VBA code that would read in the ID's and execute the SAS Add in Query that would join information to the ID numbers. Lastly it would need to export the information to the same excel file. I am experienced in SAS, but not so much with VBA and stored processes. Any guidance would be appreciated.

Thanks

You could have SAS update some hidden excel file that is then read into the excel file with your button. That way you wouldn't run into a situation where SAS is trying to update a locked Excel file (such as one that is already opened.)

Re: I have a question about the sas add in for excel

TimBeese — Wed, 03 Apr 2019 20:33:16 GMT

You can do this with a stored process using the SAS Add-In for Microsoft Office. Your list of IDs would be an input stream that you could send to the stored process to join the information to the ID numbers. Then, the results could be directed to a new worksheet (or an existing one) in the same workbook.

What's better, is that if your list of IDs changes, once you've set everything up, all you would have to do is refresh your results and it will pull the new data, run the stored process and update the results.

For a good example of how to create a stored process that takes an input stream, you can check out this whitepaper:

https://support.sas.com/resources/papers/proceedings16/SAS3500-2016.pdf

This paper has two parts, the first is about Visual Analytics reports in Excel. The second part is about stored processes in Excel. It goes through an example and shows you what you'll need to do to set it up. If you have any questions about doing this, feel free to reach out to me.

Good Luck,

Tim

Re: I have a question about the sas add in for excel

sustagens — Tue, 17 Dec 2019 00:52:47 GMT

Try this step by step procedure with input streams http://support.sas.com/kb/42/983.html

This is also an excellent reference http://support.sas.com/resources/papers/proceedings11/012-2011.pdf