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
What does your log say? No error messages?
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
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;
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
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.
Thanks a lot for your support.
If possible please try to post the log file .
Thanks,
Mahesh P
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; |
Hi All,
The only update from tech Support is ODStagsets.tableditor cannot be run in batchmode.
Thakns,
Mahesh P
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
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
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
Hi,
Did you solve the problem? I have a same problem too
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.