Move SAS data to Excel but preserve Excel Table

[robulon]

Hi,

I've done a lot of searching on this and have not been able to find the answer so am asking the experts.

I have a number of pieces of monthly MI that I produce. I produce the updated data in SAS for the new month, then export it into Excel. I currently copy and paste the exported data into an Excel template file which has the data on the first tab, in the form of an Excel table (as in Insert Table). I then pull out the specific data I need for different charts using the SUMIFS formula and subsequent tabs and then produce charts from those tables. I use the Table as in my SUMIFS formulas I can just use the Excel column as the lookup reference, whereas in a standard worksheet, I have to apply the formula to a specific range of cells.

What I am trying to work out how to do is to export the data directly into my template whilst preserving the Table in Excel.

Is this actually possible? I know proc export just replaces the data and formatting, and I've had a look at some possible solutions that I've found online such as using ods but unless I'm doing it wrong (which I freely admit is entirely possible) I can find a way to add the data into the table. I don't need to retain the existing data in the Excel table as I overwrite it each month, but do want the table format to remain in place.

As always, any suggestions or advice will be gratefully received.

Many thanks,

Rob

[ballardw]

What sort of graphs are you producing in Excel that aren't possible to make in SAS?

You could use ODS Excel to send results of Proc Print/Report to make a pretty table and SAS graphics procedures to create the graphs in an Excel file.

If you provide an example with dummy data if the values are sensitive you may get more targeted help.

[robulon]

Thanks,

The simple answer to that is that I have no doubt that the graphs I want to make are possible to make in SAS but at this stage, I am not clued up sufficiently in creating graphs with SAS to do this. Something to look at in the future though.

[Reeza]

You can use PCFILES to write to a specified range that will overwrite your source data and as long as your formulas and charts don't need to change then it'll be fine.

I would agree that you're better of summarizing your data in the long run and exporting the summarized data rather than the raw data and/or even creating the graphs in SAS and push to Excel. The drawback for that option is you lose the formula references and linkage between graphs and data. But taking an Excel template that's well designed and exporting SAS data to generate reports that are semi-automated and look good is something I've done dozens of times.

[robulon]

Thanks Reeza, I'll look into doing it that way (no doubt I'll be back on asking for more help presently!).

[DWilson]

If you have SAS running on a windows PC with excel you can use DDE within your SAS program to do what you want.

if you have SAS running on Linux then you'd have to use a SAS PCFILES server where you can use DDE.

If neither of those situations apply, then there is no direct SAS-supported method for doing what you want.

If you can install R on the computer/server where SAS runs, you could do the following:

-In your SAS program you pass the data you want to write to excel to R vis SAS IML

-You pass the R code from within SAS to R via IML that will write your SAS data to the excel file

-You must use an R package that supports writing data to excel in such a way as to preserve the excel formatting/VBA code, etc.

I know of two R packages that can do this:  openxlsx and XLConnect

See this paper for example code to get you started:
https://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-109_Final_PDF.pdf

[robulon]

Thanks DWilson, I've inherited the SAS code that I'm using for this but I think as it stands at the moment, the data is produced on a Linux server, then moved to a Windows folder using a data _null_ step with file and infile statements. It sounds as though I could use DDE once the data has been moved to the Windows location but I'll need to look into that further.

I have had learning R on my to do list for some time but not got round to it yet. I hadn't appreciated that I could still create the data in SAS and pass it to R, I'd assumed I would have to build the queries from scratch in R so another one for me to look into.

Thanks for your help.

[ballardw]

---

@robulon wrote:

Thanks DWilson, I've inherited the SAS code that I'm using for this but I think as it stands at the moment, the data is produced on a Linux server, then moved to a Windows folder using a data _null_ step with file and infile statements. It sounds as though I could use DDE once the data has been moved to the Windows location but I'll need to look into that further.

I have had learning R on my to do list for some time but not got round to it yet. I hadn't appreciated that I could still create the data in SAS and pass it to R, I'd assumed I would have to build the queries from scratch in R so another one for me to look into.

Thanks for your help.

---

Before going the DDE route you might want to search this forum for EXCEL DDE to find how many people are having issues with something Microsoft is not really advocating anymore.

[DWilson]

Microsoft and SAS may not be advocating DDE anymore but they, especially SAS, have really dropped the ball on coming up with a replacement.

Being able to write to a "cell" in an existing excel file without changing existing formats is something that SAS should be able to support. We're talking XML files here and I can think of a few ways that SAS could support that but they don't.

The hoops required to change existing SAS programs to not use DDE are substantial. Furthermore, with new programs there is no way to easily mimic DDE functionality and the only recommendations I've heard from SAS is to substantially re-work programs and processes which is an answer but not an acceptable answer.

[Reeza]

If you're 27 or younger DDE is older than you. 

Excel locked it down due to security concerns in December 2017. You can re-enable it if you need to, but I generally don't recommend doing that at all. ODS Excel is much easier to work with and less likely to break. And it will only be developed from here on out - until something else replaces it of course :).

[DWilson]

SAS just needs to modify ODS EXCEL to permit writing to a single cell at a time to an existing XML (excel) file.

If some people can do this on their own time by writing R packages then I'm sure SAS, as the largest privately held software company in the world, can do it.

[Reeza]

Have you added it to the SASware Ballot?

[DWilson]

I haven't because I don't know what that is 🙂 I'll be glad to add if you happen to be able to point me in the right direction.

[Reeza]

Top right corner of the forum, under Find a Community is the SASware Ballot, you can add feature requests essentially. And they respond, saying something will be considered, is in development or will not be considered :).

[robulon]

Thank you to all for your input, I've followed Reeza's suggestion for the time being of trying to create a robust excel template that will allow me to export my data using proc export and automatically populate the cells I need for my charts.

This however has led on to a very frustrating issue using proc export but i'll post about that separately ...