DATA Step, Macro, Functions and more

Create a Pivot table using BASE SAS

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

Create a Pivot table using BASE SAS

Hi All,

 

I am trying to create a Pivot table on Excel using SAS, i am working on SAS EG/ SAS 9.4 on

Operating System: LIN X64.

 

We cant use DDE here as it only supports/works on windows, i hav tried using the

ods tagsets.tableeditor but theis job needs to be run in batch mode the pivot table cannot be created.

I have also tried creating the Pivot using VB-Script for some reasons its not working.

 

Couls you provide me your valuable suggestions to get the pivot tables working?

 

Regards,

Santosh...

 


Accepted Solutions
Solution
‎03-01-2017 11:22 AM
Frequent Contributor
Posts: 103

Re: Create a Pivot table using BASE SAS

Hi,

 

I use the ExcelXP Tagset on a windows system to create my excel data sheet as XML. Using SAS I create a VBA file that performs the Pivot and saves the file as native XLSX file, refreshes the file(required by excel to get the pivot filter to work) and saves it again in place. Afterwards the VBA deletes the XML and itself. In the VBA I am performing many other checks such as checking if the XML file exists and can be edited (sometimes it is slow on networks)

 

I use excelXP because of its proven, working and highly customizable functionalities.

 

The VBA support websites are legion on the internet. An excellent paper on how to write the VBA for the pivot can be found here: http://www.mwsug.org/proceedings/2011/dataviz/MWSUG-2011-DG10.pdf

 

You will need to check if it works the same on linux.

Cheers,

________________________

- That still only counts as one -

View solution in original post


All Replies
Contributor
Posts: 43

Re: Create a Pivot table using BASE SAS

"Proc transpose" makes pivot table in sas. Then export table as excel file. You can use that excel in slides or link with other sheets. For proc transpose is this link is useful.i tried and it works.

http://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
Contributor
Posts: 66

Re: Create a Pivot table using BASE SAS

Hi Yavuz,

Thank you for your suggestions!!!!

i need to create an interactive Pivot table, while using the Transpose options i can only align / reshape the variables and observations.

 

Super User
Posts: 17,771

Re: Create a Pivot table using BASE SAS

Tagsets.TableEditor can run from batch, I don't know any reason it couldn't.

 

To create a native pivot table, you'll need to interface with Excel somehow. One suggestion, is to create the pivot table in Excel as a template, but update the data behind the table and force the excel file to update when it's opened. This would be export into an existing file and should work fine in Unix or Windows. 

 

Ideally, a VB Script or DDE would be the solution here.  

 

Super User
Posts: 17,771

Re: Create a Pivot table using BASE SAS

When you say 'PIVOT' table what are you referring to, a cross tabulation table or an interactive pivot table within Excel? 

 

Contributor
Posts: 66

Re: Create a Pivot table using BASE SAS

Hi Reeza,

 

An interactive pivot table on Excel. I have also tried creating a pivot table using

ods tagsets.tableeditor,as i have mentioned earlier the job needs to be run in batch mode, and beacuse of the Active X and Java pop-ups the pivot is not getting created.

 

PROC Star
Posts: 7,357

Re: Create a Pivot table using BASE SAS

Post your vbs script. Possibly one of us can identify why it isn't working. A nice simple example can be found at:

https://listserv.uga.edu/cgi-bin/wa?A2=ind0506a&L=SAS-L&O=D&F=P&X=FB41D225061C8C7F9B&P=62191

 

Art, CEO, AnalystFinder.com

Contributor
Posts: 66

Re: Create a Pivot table using BASE SAS

hi,

 

Please find the vba script which i want to call using sas, i wiil be adding the Pivot creation part.

 

 

Super User
Posts: 17,771

Re: Create a Pivot table using BASE SAS

Do you have x command enabled? 

 

If so, you can run the script using that, but not sure how VBA works on Unix. 

 

I'm not going to download a script and it doesn't matter really what's in the script if you don't have a way to run it. 

Solution
‎03-01-2017 11:22 AM
Frequent Contributor
Posts: 103

Re: Create a Pivot table using BASE SAS

Hi,

 

I use the ExcelXP Tagset on a windows system to create my excel data sheet as XML. Using SAS I create a VBA file that performs the Pivot and saves the file as native XLSX file, refreshes the file(required by excel to get the pivot filter to work) and saves it again in place. Afterwards the VBA deletes the XML and itself. In the VBA I am performing many other checks such as checking if the XML file exists and can be edited (sometimes it is slow on networks)

 

I use excelXP because of its proven, working and highly customizable functionalities.

 

The VBA support websites are legion on the internet. An excellent paper on how to write the VBA for the pivot can be found here: http://www.mwsug.org/proceedings/2011/dataviz/MWSUG-2011-DG10.pdf

 

You will need to check if it works the same on linux.

Cheers,

________________________

- That still only counts as one -

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 723 views
  • 1 like
  • 5 in conversation