11-21-2016 05:08 AM
Following recommendations, I am using now SAS enterprise guide for server. Unfortunately my SAS macros using DDE for opening specific files (with TDMS extension) with excel and save this files in excel format, are no longer working. SAS just confirms me that DDE programs don't work anymore with SAS entreprise guide due to restristion coming from Microsoft. Is there any other possibility for opening excel file and save as in specific path through SAS programs?
Thanks for your help.
11-21-2016 05:30 AM
With SAS 9.4, you can use a libname to an Excel file, you can write Excel files with ODS (including formatting), or you can use proc export to create xlsx files. This is platform-independent, since it uses the open office formats that are (technically) XML files in a compressed archive and do not require MS-supplied Windows-only modules.
Note that, with server-based SAS, you can only create files on resources that the SAS server can "see".
You can use the Export task in SAS Enterprise Guide to save Excel files directly to your desktop (or a shared resource that is mounted on your desktop)
Depending on the operating system of your server, DDE might work there (Windows), but you would also need Excel being installed there. Note that DDE is old technology that has been superseded by newer mechanisms (COM, parts of .NET).
11-21-2016 05:53 AM
Yes, DDE is finally disappearing, it was stopped support back pre Office 2003 and in a fair few scenarios doesn't work now. As @KurtBremser has mentioned there are various methods to get data to Excel, including also SAS Office plugin.
I would ask, is Excel really necessary? In most (if not all) scenarios there are better data transport file formats. If there really is a need to use Excel, then you could also consider doing the work in Excel, which has a full language - VBA - available. Dump your data out as CSV, then in Excel have a VBA macro which opens the CSV and processes it into the required format. Whilst SAS makes every attempt to provide tools to get data out to Excel, the sheer uncontrolled possibilities of Excel (just thinkg you could put anything, objects, pictures anything in each cell) its quite difficult to reproduce every possibility.
So as above, I tend to look at reporting environments if the outputs are reports and data transfer formats if its for data transfer.
11-21-2016 06:43 AM
When you arrive at such a point in your Data Warehousing Lifecycle, take the time to go back to your initial requirements and evaluate:
- what data needs to be provided
- who needs to have access to the data
- if further processing of the data is done, can/should such processing be integrated into the DWH (better control & documentation)
Then you should start to contemplate which tools are best suited for your requirements. At this stage you might find that creating a web-viewable report might be much better suited to solve the task than the common Excel-detour.
A table in a web page can easily be opened with or copy/pasted to a spreadsheet calculator.
In our organization, we strongly discourage the use of Excel for BI tasks. Formatting, displaying, creating graphs and reports is OK, but data manipulation has to be done in SAS. SOX II et al actually mandate this, when you look closely.