BookmarkSubscribeRSS Feed
apeksha
Fluorite | Level 6

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

9 REPLIES 9
Reeza
Super User

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. 

Reeza
Super User

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.

Cynthia_sas
SAS Super FREQ

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

apeksha
Fluorite | Level 6

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

Reeza
Super User

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. 

 

 

apeksha
Fluorite | Level 6

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 

apeksha
Fluorite | Level 6

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

Cynthia_sas
SAS Super FREQ
HI:
I am not sure what you mean by "two graphs stacked over one another" -- I do not believe that ODS EXCEL gives you that kind of placement control. I think this is a question for Tech Support.

Also, I am guessing your sizes for WIDTH and HEIGHT are not supported for the destination you are using and so they are being ignored. This would be another question for Tech Support (is there a way to increase the size of the graph).

cynthia
Cynthia_sas
SAS Super FREQ
It was my understanding that MSOFFICE2K created Microsoft HTML and that MSOFFICE2K_X created Microsoft HTML with XML and possibly used some embedded JavaScript.

Neither of the MSOFFICE2K-based destinations create true binary Excel files. Only ODS Excel creates an XLSX file (or using PROC EXPORT or the Excel LIBNAME engine.)

cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2028 views
  • 2 likes
  • 3 in conversation