Help using Base SAS procedures

Pivot table in SAS

Reply
Contributor
Posts: 31

Pivot table in SAS

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,


Super User
Super User
Posts: 7,942

Re: Pivot table in SAS

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.

Super User
Posts: 19,787

Re: Pivot table in SAS

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

Occasional Contributor
Posts: 17

Re: Pivot table in SAS

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

Super User
Posts: 19,787

Re: Pivot table in SAS

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

Super User
Posts: 3,252

Re: Pivot table in SAS

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

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

Ask a Question
Discussion stats
  • 5 replies
  • 1567 views
  • 0 likes
  • 5 in conversation