Hi,
I am currently manually creating a pivot table using an XLS file and sent it to users. I need to automate it and looking for suggestions. I am using PC SAS.
Thanks,
Well, there are two or three options. Unfortunately this is a question which comes up a lot - I have xyz in Excel and I want to do something in SAS then have Excel updated. So some options:
- Learn Open Office format - this is what is used to store Excel files behind the scenes - rename the XLSX to ZIP and you can see the folders and files. This is really in depth however.
- Use DDE to send your data out to an existing file - This is old tech and may/may not work in future and isn't straight forward.
- Save your data from SAS as CSV. Then in your Excel file have a vba macro which imports the CSV data and updates your sheet.
As far as I am aware there is no way to create a pivot chart from directly in SAS. The output is for reports only.
Do you need to create an actual pivot table and send it out, or a table with information in a specific format?
This doesn't create a native excel file, and I think its ugly but its a solution:
Base SAS: Creating a Data Grid Like VB.NET
Excel Hacks:
Create the excel file manually (once). Update the data via an export. When user opens the workbook they will have to refresh (usually via a pop up), or embed a macro or script in the workbook to automatically open on refresh.
DDE: Combination of export and then refresh using DDE
Here is another link which may be useful, similar to Reeza's first option:
http://support.sas.com/resources/papers/proceedings14/SAS177-2014.pdf
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.