topic Re: Unable to create excel file while using code for pivot tables in Graphics Programming

Unable to create excel file while using code for pivot tables

MaheshPeesari — Tue, 07 Aug 2012 09:08:00 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

Thanks,

Mahesh P

Re: Unable to create excel file while using code for pivot tables

Reeza — Tue, 07 Aug 2012 16:23:49 GMT

What does your log say? No error messages?

Re: Unable to create excel file while using code for pivot tables

Cynthia_sas — Tue, 07 Aug 2012 16:42:32 GMT

Hi:

If you look on page 7 of that paper, you will find out how ODS TAGSETS.TABLEEDITOR creates the PIVOT table:

quote (highlighting and underlining is mine):

"In order to create a PivotTable you wrap some ODS around code that will create the data table upon which the

PivotTable is based. The ODS will cause SAS will create an HTML page with the data along with a command button.

Through the use of JavaScript the code behind the button on the HTML page will generate the necessary objects to

start Excel and create the PivotTable using the ODS options you supply. For more discussion on the TableEditor

tagset refer to the papers by Parker cited in the references."

Basically, when you use ODS methods, you are creating ASCII text files (HTML, HTML+JavaScript, CSV or XML files) that Excel knows how to open and render. The authors of that paper noted that their examples were all run in SAS 9.2 and they noted some issues with SAS 9.3. If you have problems you can do this:

1) contact the paper authors for help with their programs (which were tested in 9.2)

2) if you are running your program in a version other than 9.2, work with Tech Support

cynthia

Re: Unable to create excel file while using code for pivot tables

MaheshPeesari — Tue, 07 Aug 2012 18:35:41 GMT

Hi Cynthia,

I have used AUTO_EXCEL="Yes" ,but still i am unable to get the excel file generated without touching the "export tab" from result viewer.I am using SAS9.2,do they mind if i contact them directly.All i need is, when pivot tables are generated they have to automatically get created into the respective folders without touching the Export Button,once the data is loaded it has to perform automatically,no manual intervention should take place.

Thanks a lot for your guidance.

Thanks,

Mahesh P

Re: Unable to create excel file while using code for pivot tables

MaheshPeesari — Tue, 07 Aug 2012 18:38:37 GMT

There is no error message in the log file,Please check the below log file:

301 ods tagsets.tableeditor file="&resultsHome\example42.html"

SYMBOLGEN: Macro variable RESULTSHOME resolves to E:\HoW\Eberhardt-Kong_13173\results

302 options(

303 AUTO_EXCEL="Yes"

304 button_text = "Create PivotTable"

305 auto_excel = "yes"

306 pivotrow = "product_line"

307 pivotcol = "year"

308 pivotdata = "profit,quantity"

309 pivotdata_stats = "sum,average"

310 excel_save_file="&JavaResultsHome\example42.xls"

SYMBOLGEN: Macro variable JAVARESULTSHOME resolves to

E:\\HoW\\Eberhardt-Kong_13173\\results\

311 );

NOTE: Writing TAGSETS.TABLEEDITOR Body file:

E:\HoW\Eberhardt-Kong_13173\results\example42.html

V2.55 4/11/2012

312 Title1 "Example 2 - Change the Statistics";

313 proc print data=sashelp.orsales;

314 run;

NOTE: There were 912 observations read from the data set SASHELP.ORSALES.

NOTE: PROCEDURE PRINT used (Total process time):

real time 1.96 seconds

user cpu time 1.89 seconds

system cpu time 0.00 seconds

Memory 426k

OS Memory 11432k

Timestamp 8/8/2012 12:05:58 AM

315 ods tagsets.tableeditor close;

Please give your inputs.

Thanks,

Mahesh P

Re: Unable to create excel file while using code for pivot tables

Cynthia_sas — Tue, 07 Aug 2012 19:33:54 GMT

Hi:

I would actuallly recommend working with Tech Support. They can have you check your version of the TAGSET template (whether you have the most current version or not), they can look at all your code and they can test your code in SAS 9.2 (for example, I only run examples in SAS 9.3 because I don't have SAS 9.2 anymore since all our classes are conducted in 9.3).

To open a track with Tech Support, fill out the form at this link: http://support.sas.com/ctx/supportform/createForm

If the paper authors provided contact information at the end of their paper, then they probably do not mind if you contact them. However, they do have other jobs and other responsibilities. So they might not have time to help you individually or in great detail. On the other hand, Tech Support will open a track for you and will stick with you until you get some resolution -- that's what they do!

cynthia

Re: Unable to create excel file while using code for pivot tables

Reeza — Tue, 07 Aug 2012 21:40:08 GMT

For the record, running the code below gives a an Activex error and a IE script error but it does create the Excel file automatically on SAS 9.3

Try using the xls extension, but at the end of the day, Cynthia is correct Tech Support is your best bet because your IE settings or virus software could be stopping the script.

ods tagsets.tableeditor file='C:\temp\table.html'

options (auto_excel="Yes"

button_text='Create PivotTable'

pivotrow='product_line'

pivotcol='year'

pivotdata='profit,quantity'

pivotdata_stats='sum'

excel_save_file="C:\temp\table.xls");

proc print data=sashelp.orsales;

run;

ods tagsets.tableeditor close;

Re: Unable to create excel file while using code for pivot tables

MaheshPeesari — Wed, 08 Aug 2012 01:46:35 GMT

HI Reeza,

IS that file creAted dynamically or else did you go To the result viewer and then press the create pivot table button.because even for me fle gets created only after pressing the button in result viewer.i want this to be automated because I will execute this code on PC SAS,and moreover all the code will be scheduled.

I want to add an action immediately after loading the page I,e I E so that the excel file gets created once the page is Loaded.

THanks for your help,as suggested I will raise a track with SAS Tech Support.

Thanks,

Mahesh P

Re: Unable to create excel file while using code for pivot tables

Reeza — Wed, 08 Aug 2012 15:39:52 GMT

The file was created automatically, I did have to clear the errors though, so I'd first have to work out those somehow.

I did not press the button on the webpage.

Re: Unable to create excel file while using code for pivot tables

MaheshPeesari — Thu, 09 Aug 2012 05:23:15 GMT

Thanks a lot for your support.

If possible please try to post the log file .

Thanks,

Mahesh P

Re: Unable to create excel file while using code for pivot tables

Reeza — Thu, 09 Aug 2012 15:22:56 GMT

I doubt how this will help, but there you go.

2559

2560

2561

2562 ods tagsets.tableeditor close;

2563 ods tagsets.tableeditor file='C:\temp\table.html'

2564 options (auto_excel="Yes"

2565 button_text='Create PivotTable'

2566 pivotrow='product_line'

2567 pivotcol='year'

2568 pivotdata='profit,quantity'

2569 pivotdata_stats='sum'

2570 excel_save_file="C:\temp\table.xls");

NOTE: Writing TAGSETS.TABLEEDITOR Body file: C:\temp\table.html

V2.55 4/11/2012

2571

2572 proc print data=sashelp.orsales;

2573 run;

NOTE: There were 912 observations read from the data set SASHELP.ORSALES.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.59 seconds

cpu time 0.50 seconds

2574

2575 ods tagsets.tableeditor close;

Re: Unable to create excel file while using code for pivot tables

MaheshPeesari — Tue, 14 Aug 2012 11:08:35 GMT

Hi All,

The only update from tech Support is ODStagsets.tableditor cannot be run in batchmode.

Thakns,

Mahesh P

Re: Unable to create excel file while using code for pivot tables

yukki — Sun, 15 Nov 2020 13:01:54 GMT

Hi,

Did you solve the problem? I have a same problem too