BookmarkSubscribeRSS Feed
Wentzwagon11
Calcite | Level 5

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

4 REPLIES 4
Rajesh3
Obsidian | Level 7

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.

DWilson
Pyrite | Level 9

@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.)

TimBeese
SAS Employee

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

sustagens
Pyrite | Level 9

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 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1028 views
  • 0 likes
  • 5 in conversation