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...
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,
- Cheers -
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.
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.
When you say 'PIVOT' table what are you referring to, a cross tabulation table or an interactive pivot table within Excel?
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.
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
hi,
Please find the vba script which i want to call using sas, i wiil be adding the Pivot creation part.
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.
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,
- Cheers -
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.