How to write Unix Sas result into a specific range in Excel

Question

I have SAS installed on Unix (not on PC) and I want to create an excel template, upload it to Unix and let SAS write values into specific and preformatted ranges/cells. How can I do that?

I don't think DDE(Dynamic data exchange) or Excel libname engine will work because these two methods cannot communicate the local PC applications (Excel.exe). But then ODS and proc export cannot really control which cell SAS should export the outputs to. Is there any solutions to this problem?

Thanks in advance!

Answer 1

If I was you I'd write your SAS code as a Stored Procedure, which outputs your data in tab delimited format to the _webout fileref, then call it as a web query from Excel using VBA. You'll have much better control over what gets populated in the workbook, and no issues with unix compatibility. You can see a guide to this approach here.

Answer 2

The way I would do this if I were to go with the template route - which I would not if I could avoid it - is to read the template into SAS as a data set. Once it's a data set you can populate the cells as needed (via SAS merge/update/whatever), and then export the populated template. You would then use VBA or similar in Windows-land to get any formatting done.

Better, though, would be using ODS EXCEL and doing the formatting there.