BookmarkSubscribeRSS Feed
archana
Fluorite | Level 6

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,


5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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

haikuobian
Fluorite | Level 6

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
Super User

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
PROC Star

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3461 views
  • 0 likes
  • 5 in conversation