Unable to create excel file while using code for pivot tables

[MaheshPeesari]

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

[Reeza]

What does your log say? No error messages?

[MaheshPeesari]

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

[Reeza]

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;

[MaheshPeesari]

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

[Reeza]

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.

[MaheshPeesari]

Thanks a lot for your support.

If possible please try to post the log file .

Thanks,

Mahesh P

[Reeza]

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;

[MaheshPeesari]

Hi All,

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

Thakns,

Mahesh P

[Cynthia_sas]

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

[MaheshPeesari]

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

[Cynthia_sas]

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

[yukki]

Hi,

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