Hi,
i am using msoffice2k_x to have multiple excel sheets to my excel. But the output is always empty.
I am not getting any error as well. Any help?
ods path(prepend) work.templat(update);
%include "\\10.10.2.51\xxx\SAS Automation\base codes\msoffice2k_x.sas";
ods tagsets.msoffice2k_x path="\\10.10.2.51\xxx\SAS Automation" file="report.xls" style=statistical
options(worksheet_source="C:\temp\daily.html,
C:\temp\weekly.html");
data _null_;
file print;
put _all_;
run;
ods tagsets.msoffice2k_x close;
both HTMLS exist and when i use the msoffice2k option to print the htmls the results are fine. But the msoffice2k_x is giving me an empty sheet!
Any help is appreciated!
Thanks,
Apeksha Pathak
Your looking to generate multiple excel sheets in one file? That isn't available by default in MSoffice2k. Can you use tag sets.excelxp or if on SAS 9.4, ODS Excel?
Also, please post your log from code above.
Also, does the sample code work for you?
http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html#multiple
Then you can determine if it's an issue with your code or the template.
i notice the for the OP, the PATH= option is to a server location, but the 2 files identified as the worksheets are on c:\temp. Do those files already exist? Can the server 10.10.2.51 read the C: drive location, as it is coded, or does the C: drive have another way to specify path on the network, such as an IP address or a machine name? As long as the files used for the worksheet source exist, and my "main" report file are all on the same machine, the example code works for me. I have never tried what the OP is doing though and I am not sure whether it will work.
cynthia
Thanks Cinthia/Reeza for your reply.
Reeza, i cant use EXCELXP would create an XML and i need to attach an excel and send a mail to my client. They dont want to download an XML and open it with Excel. They are finicky that way. I havent tried my luck with ODS Excel yet. I am doing it now.
The Sample code doesnt work for me either. I get an XLS with only test written on one sheet.
Cinthia, here is the log of my code.
NOTE: PROCEDURE PRINTTO used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
25
26 %put short version: &sysver;
short version: 9.4
27 %put version: &sysvlong4;
version: 9.04.01M2P07232014
28 %put site #: &syssite;
site #: 11603085
29
30 %include "\\10.10.2.51\xxx\SAS Automation\base codes\msoffice2k_x.sas";
NOTE: Overwriting existing template/link: Tagsets.Msoffice2k_x
NOTE: TAGSET 'Tagsets.Msoffice2k_x' has been saved to: WORK.TEMPLAT
NOTE: PROCEDURE TEMPLATE used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
1219
1220 proc sort data=sashelp.class out=test;
1221 by age;
1222 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.TEST has 19 observations and 5 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
2 The SAS System 11:21 Monday, February 15, 2016
1223
1224 ods tagsets.msoffice2k path="\\10.10.2.51\xxx\SAS Automation\" file="temp.html" newfile=output;
NOTE: Writing TAGSETS.MSOFFICE2K Body file: temp.html
1225
1226 proc print data=test;
1227 by age;
1228 run;
NOTE: Writing TAGSETS.MSOFFICE2K Body file: temp1.html
NOTE: The above message was for the following BY group:
Age=12
NOTE: Writing TAGSETS.MSOFFICE2K Body file: temp2.html
NOTE: The above message was for the following BY group:
Age=13
NOTE: Writing TAGSETS.MSOFFICE2K Body file: temp3.html
NOTE: The above message was for the following BY group:
Age=14
NOTE: Writing TAGSETS.MSOFFICE2K Body file: temp4.html
NOTE: The above message was for the following BY group:
Age=15
NOTE: Writing TAGSETS.MSOFFICE2K Body file: temp5.html
NOTE: The above message was for the following BY group:
Age=16
NOTE: There were 19 observations read from the data set WORK.TEST.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.11 seconds
cpu time 0.11 seconds
1229
1230 ods tagsets.msofice2k close;
1231
1232 ods tagsets.msoffice2k_x file="\\xxx\Scienpatic_working\SAS Automation\multiple.xls" style=statistical
1233 options( worksheet_source="11#\\xxx\Scienpatic_working\SAS Automation\temp.html,
1234 12#\\xxx\Scienpatic_working\SAS Automation\temp1.html,
1235 13#\\xxx\Scienpatic_working\SAS Automation\temp2.html,
1236 14#\\xxx\Scienpatic_working\SAS Automation\temp3.html,
1237 15#\\xxx\Scienpatic_working\SAS Automation\temp4.html,
1238 16#\\xxx\Scienpatic_working\SAS Automation\temp5.html");
NOTE: Writing TAGSETS.MSOFFICE2K_X Body file: \\10.10.2.51\xxx\SAS Automation\multiple.xls
v2.75
11#\\10.10.2.51\xxx\SAS Automation\temp.html
1239
1240 data _null_;
1241 file print;
1242 put "test";
1243 run;
NOTE: Writing TAGSETS.MSOFFICE2K Body file: temp6.html
NOTE: 1 lines were written to file PRINT.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.04 seconds
Do you get an error when you open your excel file? About missing paths?
Get the demo working and that should guide you on how to set up your paths/files.
Also, is MSOFFICE actually generating a native Excel file? It has the xls extension but I thought it was still a different type of file.
Hi Reeza,
Yes an XLSX is being created and has no errors while i am trying to open it. As my work is very time dependent, i have shifted my apporoach to ODS excel. Thanks so much for suggesting.
I am trying to print different sheets by using "sheet_interval = proc" in options. But my sheets are being created with names like this -"Summary-Tabulate 1" "Summary - Tabulate 2".
I have tried other options too, do you have any idea how i can customise the sheet name in ODS excel?
Here's my code:
ods escapechar='~';
/* CREATES a new XLSX file */
ods excel (id=fb) file=fbout
/* choose a style you like */
style=pearl
/* SHEET_INTERVAL of NONE means that each PROC won't generate a
new sheet automatically */
options (sheet_interval ="proc" sheet_label="Summary")
;
/*ods noproctitle;*/
/*ods text="daily summary";*/
proc tabulate data = test.daily;
class Year Day;
var No_of_hhs Viewing_hhs Perc_Viewing_HH Average_Viewers;
table Year,
(Day=' ' *(No_of_hhs Viewing_hhs Perc_Viewing_HH*F=PERCENT7.2 Average_Viewers));
Keylabel Sum=' ';
run;
/*ods noproctitle;*/
/*ods text="weekly summary";*/
proc tabulate data = test.weekly;
class Year;
var No_of_hhs Viewing_hhs Perc_Viewing_HH Average_Viewers;
table Year,
(No_of_hhs Viewing_hhs Perc_Viewing_HH*F=PERCENT7.2 Average_Viewers);
Keylabel Sum=' ';
run;
ods excel (id=fb) close;
I will now add graphs and tables also in the excel. Will come back with more questions!
Thankyou for all the help! I appreciate it!
Thanks,
Apeksha
While using gplot, when i am exporting my results in excel, the graph is coming really small.
ods graphics on / width=20in height=10in; <- this is the option i have used.
Its hardly 6*7 inches. Issue is, the plot will dynamically created for every week and as week increases the graph will become cluttered.
Is there a way to increase the size if the graph?
I also want to have two graphs stacked over one another, how can i do that? I have to achive all this is Excel.
Any help is much appreciated.
Thanks,
Apkesha Pathak
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.