topic Re: Pivot table editor no hands excel report in ODS and Base Reporting

Pivot table editor no hands excel report

yukki — Sun, 15 Nov 2020 13:09:22 GMT

Hi All,

I am using the below code ,when i run this code i am unable to create excel sheet,is it not possible to create excel sheet when we run ODStagsets+tableeditor options,or else do i have to click, export the reults from HTML page and then save it,what if i want to run the code in PC SAS,can anyone of you please help out.

this code is taken from http://support.sas.com/resources/papers/proceedings12/146-2012.pdf

Actually i have to run all this code on PC SAS,if there is no way in creating excel sheet ,can you please give me some idea's as how it can be achieved.

%let home = C:\HoW\Eberhardt-Kong_13173;

%let solutionHome = &home.\solutions;

%let exerciseHome = &home.\exercises;

%let dataHome = &home.\data;

%let resultsHome = &home.\results;

%let javaHome = C:\\HoW\\Eberhardt-Kong_13173\;

%let javaDataHome = &javaHome.\data\;

%let javaResultsHome = &javaHome.\results\;

/* assign the libraries */

libname data "&dataHome";

options fullstimer source source2;

ods tagsets.tableeditor file="&resultsHome\example1.html"

options(

button_text = "Create PivotTable"

auto_excel = "NO"

pivotrow="product_line"

pivotcol="year"

pivotdata="Profit,Quantity,Total_Retail_Price"

pivotdata_stats=”Sum,Average,Max”

excel_save_file="&JavaResultsHome\example1.xlsx"

quit="yes"

);

Title1 "Example 1 - Create the First Pivot Table";

proc print data=data.sales;

run;

ods tagsets.tableeditor close

Re: Pivot table editor no hands excel report

Kurt_Bremser — Sun, 15 Nov 2020 13:16:53 GMT

Please supply your source data in usable form (data step with datalines), and an example for what you expect as result. We might then be able to show you how to do it with ODS EXCEL and the proper SAS procedure(s).

Re: Pivot table editor no hands excel report

yukki — Sun, 15 Nov 2020 14:15:57 GMT

Hi,

I am using Sas Enterprise Guide. and i want to make pivot table excel reports automatically and save them ( without touching the html "create pivot table " button.

*/ /* the autoexec to start the workshop */

%let home = C:\HoW\Eberhardt-Kong_13173;

%let solutionHome = &home.\solutions;

%let exerciseHome = &home.\exercises;

%let dataHome = &home.\data;

%let resultsHome = &home.\results;

%let javaHome = C:\\HoW\\Eberhardt-Kong_13173\;

%let javaDataHome = &javaHome.\data\;

%let javaResultsHome = &javaHome.\results\

Filename tpl url "http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";
ods path(prepend) work.templates(update);
%include tpl;

ods tagsets.tableeditor file="&resultsHome/example2.html"
options(button_text = "Create PivotTable"
auto_excel="yes"
excel_orientation="landscape"
pivotrow="AGE"
pivotcol="SEX"
pivotdata="AGE"
pivotdata_stats="COUNT"
excel_save_file="&JavaResultsHome/example2.xlsx"
file_format="xlsx"
);
proc print data=SASHELP.CLASS;
var age sex Height name Weight ;
title "Pivot tables";
run;
ods tagsets.tableeditor close;

Re: Pivot table editor no hands excel report

Kurt_Bremser — Sun, 15 Nov 2020 14:27:52 GMT

Since I do not have Excel available, please show me what the end result should look like.

Re: Pivot table editor no hands excel report

yukki — Sun, 15 Nov 2020 14:45:47 GMT

I gave an example from sashelp. Its a simple excel pivot table.Its not important what its look like. The problem is i cannot export an excel pivot table without clickling the button.

Sas EPG exporting pivot table without touching html button ???

yukki — Sun, 15 Nov 2020 14:49:31 GMT

Hi,

I am using Sas Enterprise Guide. and i want to make pivot table excel reports automatically and save them ( without touching the html "create pivot table " button.

*/ /* the autoexec to start the workshop */

%let home = C:\HoW\Eberhardt-Kong_13173;

%let solutionHome = &home.\solutions;

%let exerciseHome = &home.\exercises;

%let dataHome = &home.\data;

%let resultsHome = &home.\results;

%let javaHome = C:\\HoW\\Eberhardt-Kong_13173\;

%let javaDataHome = &javaHome.\data\;

%let javaResultsHome = &javaHome.\results\

Filename tpl url "http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";
ods path(prepend) work.templates(update);
%include tpl;

Re: Pivot table editor no hands excel report

Kurt_Bremser — Sun, 15 Nov 2020 15:23:24 GMT

Please do not repost a question in a new thread; it only causes confusion and does not help in solving your issue. It also makes it harder for you, as you would need to follow two separate discussion lines.

I asked you for the desired end result because I am quite confident that we can create that without involving Excel at all.

Doing Business intelligence in Excel when you have SAS at hand is like trying to move 10 tons of concrete on a bicycle while the 18-wheeler stands right next to it.

(exaggeration intended, but not much)

Re: Pivot table editor no hands excel report

yukki — Sun, 15 Nov 2020 15:39:53 GMT

In the code if you look its writing simple pivot report with age and sex counting .I wrote my problem 3 times but still you are asking what its look like.

Re: Pivot table editor no hands excel report

Kurt_Bremser — Sun, 15 Nov 2020 15:48:57 GMT

I asked for the end result because I cannot use your Excel table, as I do not have Excel available.

And since I don't work with Excel and with TABLEEDITOR HTML files, I can't infer the result from the code.

So please SHOW me the intended result (take a screenshot, for instance).

Re: Pivot table editor no hands excel report

yukki — Sun, 15 Nov 2020 16:11:39 GMT

If you dont work with table editor, Please dont waste my time asking questions.
Thank you

Re: Pivot table editor no hands excel report

SASKiwi — Sun, 15 Nov 2020 21:42:05 GMT

Have you checked the ODS community by searching with the key word tableeditor?

Also the SAS expert on ODS tagsets is @Chevell_sas who may be able to help.

Re: Pivot table editor no hands excel report

Chevell_sas — Thu, 19 Nov 2020 14:44:55 GMT

Hello,

If you want to generate the pivot table without having to select the export button from the HTML page, you can use the other method of generating the pivot table. This method requires that you point to an an existing Excel/HTML/XML/CSV file or create one that can be used as the data source to the pivot table. The TableEditor tagset can then point to this data and with options generate the JavaScript processing instructions on how to generate the pivot tables. The below blog explains this in detail, please let me know of any problems.

https://blogs.sas.com/content/sgf/2015/03/27/using-sas-to-add-pivottables-to-your-excel-workbook/

Sincerely,

Chevell