Create a Pivot table using BASE SAS

[santosh_pat69]

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...

[Yavuz]

"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/

[santosh_pat69]

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.

[Reeza]

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.  

[Reeza]

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

[santosh_pat69]

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.

[art297]

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

[santosh_pat69]

hi,

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

[Reeza]

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. 

[Oligolas · Accepted solution]

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,