Pivot table in SAS

[archana]

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,

[RW9]

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.

[Reeza]

Do you need to create an actual pivot table and send it out, or a table with information in a specific format?

[haikuobian]

Like suggested, if all you have is Base SAS, then there isn't a straightforward solution for you. However, if you are using SAS EBI and have OLAP Cube studio and SAS Microsofte add-in installed, then it becomes a piece of cake. Pivot table is nothing but a simplified Cube.

Haikuo

[Reeza]

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

[SASKiwi]

Here is another link which may be useful, similar to Reeza's first option:

http://support.sas.com/resources/papers/proceedings14/SAS177-2014.pdf