Excelxp Tagsets: is there a way to create multiple sheets on
in [SAS]
|
From: Jack Hamilton on 10 Jul 2008 21:41 Have you tried ODS tagsets.excelxp EXCLUDE ALL before the section you don't want to appear in Excel, followed by ODS tagsets.excelxp SELECT ALL to turn it back on? On Thu, 10 Jul 2008 11:55:38 -0500, "Mary" <mlhoward(a)AVALON.NET> said: > This does help, but is not exactly what I want since I have an ODS OUTPUT > statement nested in-between the two sheets that I wanted to print, and it > does print that as well (though only one sheet per proc, which is better > than I was getting before with one sheet per ODS output file!). > > I guess that I'll have to decide if it is less work to combine workbooks > or to delete sheets within one workbook if these are the only choices. > > I also noticed that I can't have an "ods_all_ close;" statement nested > inside, as it will close the tagset as well! > > Thanks very much. > > -Mary > ----- Original Message ----- > From: Satindra Chakravorty > To: Mary > Cc: SAS-L(a)listserv.uga.edu > Sent: Thursday, July 10, 2008 11:23 AM > Subject: Re: Excelxp Tagsets: is there a way to create multiple sheets > on the same file? > > > I believe the 'sheet_interval' option will do the trick. For example, > if you have several PROC steps and you want the output from each to > populate a different worksheet in the same workbook, you could add > something like... > ods tagsets.excelxp file='...' options(sheet_interval='proc'); > and a second line that goes something like this... > ods tagsets.excelxp options(sheet_name='S1'); > PROC ....; > run; > ods tagsets.excelxp options(sheet_name='S2'); > PROC ....; > run; > ods tagsets.excelxp close; > > Satindra. > > > On Thu, Jul 10, 2008 at 10:25 AM, Mary <mlhoward(a)avalon.net> wrote: > > Question on ODS Excel tagsets: > > > > I'm doing this code: > > > > ods tagsets.excelxp file='C:\Work_Activities\crosstabs_results_14.xls' > > style=statistical > > options(sheet_name='age12vs11_all'); > > run; > > proc print noobs data=results; > > run; > > ods tagsets.excelxp close; > > run; > > > > But I find if I put in the same filename with a different sheet, it deletes > > the previous file instead of adding a sheet to it. Is there a way to get > > it to add sheets to an existing file rather so I don't have to put a > > different file name down for each sheet that I want? > > > > -Mary > > -- Jack Hamilton Sacramento, California jfh(a)alumni.stanford.org <== Use this, not jfh(a)stanfordalumni.org
From: Mary on 14 Jul 2008 12:32 Jack, Thanks very much; I'll use the EXCLUDE ALL option in the future. I also discovered that if I save all the results in one data set, adding a variable called "sheetname", then I could write a macro that separated them back out at the time I was writing them to an Excel spreadsheet, and this way I was able to get my sheetnames to be the sheets on the Excel Spreadsheet instead of byline titles. %macro do_calls; ods listing close; ods tagsets.excelxp file='C:\Work_Activities\results_sheetnames.xls' style=analysis2 options(absolute_column_width='15,10,8,8,8,8,8,8,8,8,8,8,8,8' sheet_label=' '); proc sql; create table bylist as select distinct sheetname from results_all; quit; run; data bylist; set bylist; obsnum + 1; run; proc sql noprint; select count(*) into :model_count from bylist; quit; %put &model_count; %Do I = 1 %To &model_count; proc sql noprint; select sheetname into :sheetname from bylist where obsnum =&i; quit; ods tagsets.excelxp options(sheet_name="&sheetname"); data results; set results_all; if sheetname= "&sheetname"; drop sheetname; proc print noobs data=results; run; %End ; ods tagsets.excelxp close; ods listing; %mend; %do_calls; ----- Original Message ----- From: Jack Hamilton To: SAS-L(a)LISTSERV.UGA.EDU Sent: Thursday, July 10, 2008 8:41 PM Subject: Re: Excelxp Tagsets: is there a way to create multiple sheets on the same file? Have you tried ODS tagsets.excelxp EXCLUDE ALL before the section you don't want to appear in Excel, followed by ODS tagsets.excelxp SELECT ALL to turn it back on? On Thu, 10 Jul 2008 11:55:38 -0500, "Mary" <mlhoward(a)AVALON.NET> said: > This does help, but is not exactly what I want since I have an ODS OUTPUT > statement nested in-between the two sheets that I wanted to print, and it > does print that as well (though only one sheet per proc, which is better > than I was getting before with one sheet per ODS output file!). > > I guess that I'll have to decide if it is less work to combine workbooks > or to delete sheets within one workbook if these are the only choices. > > I also noticed that I can't have an "ods_all_ close;" statement nested > inside, as it will close the tagset as well! > > Thanks very much. > > -Mary > ----- Original Message ----- > From: Satindra Chakravorty > To: Mary > Cc: SAS-L(a)listserv.uga.edu > Sent: Thursday, July 10, 2008 11:23 AM > Subject: Re: Excelxp Tagsets: is there a way to create multiple sheets > on the same file? > > > I believe the 'sheet_interval' option will do the trick. For example, > if you have several PROC steps and you want the output from each to > populate a different worksheet in the same workbook, you could add > something like... > ods tagsets.excelxp file='...' options(sheet_interval='proc'); > and a second line that goes something like this... > ods tagsets.excelxp options(sheet_name='S1'); > PROC ....; > run; > ods tagsets.excelxp options(sheet_name='S2'); > PROC ....; > run; > ods tagsets.excelxp close; > > Satindra. > > > On Thu, Jul 10, 2008 at 10:25 AM, Mary <mlhoward(a)avalon.net> wrote: > > Question on ODS Excel tagsets: > > > > I'm doing this code: > > > > ods tagsets.excelxp file='C:\Work_Activities\crosstabs_results_14.xls' > > style=statistical > > options(sheet_name='age12vs11_all'); > > run; > > proc print noobs data=results; > > run; > > ods tagsets.excelxp close; > > run; > > > > But I find if I put in the same filename with a different sheet, it > deletes > > the previous file instead of adding a sheet to it. Is there a way to > get > > it to add sheets to an existing file rather so I don't have to put a > > different file name down for each sheet that I want? > > > > -Mary > > -- Jack Hamilton Sacramento, California jfh(a)alumni.stanford.org <== Use this, not jfh(a)stanfordalumni.org
From: Jack Hamilton on 15 Jul 2008 11:55 Maybe there are some invisible characters in there somewhere? Also, if you are getting a message saying that ExcelXP is experimental, you are not using the current version. You should fix that problem first. Download the latest from <http://support.sas.com/rnd/base/ods/odsmarkup/>. I save it in a file and %INCLUDE it, but there are other ways. From my system: ===== NOTE: SAS 9.1.3 SP4 (E9BX01) 1 ods tagsets.excelxp file='c:\temp\test.xls' style=statistical; NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\test.xls NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08). Add options(doc='help') to the ods statement for more information. 2 ods tagsets.excelxp options(sheet_interval='proc'); NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08). Add options(doc='help') to the ods statement for more information. ===== On Tue, 15 Jul 2008 11:30:03 -0400, "Steven Raimi" <sraimi(a)MARKETINGASSOCIATES.COM> said: > Thanks Satindra, Jack, Mary, and others on this thread - this addresses a > problem I've been working around. However, I've tried to implement this > with v9.1.3 under Windows XP, and I've been getting the following error > in > my log: > > 2545 ods tagsets.excelxp file="y:\sas\2008 ad hocs\leads sales > match\Revised Counts with 9 sale > 2545! limit &sysdate..xml" > 2546 style=statistical; > NOTE: Writing TAGSETS.EXCELXP Body file: y:\sas\2008 ad hocs\leads sales > match\Revised Counts > with 9 sale limit 14JUL08.xml > NOTE: Experimental Excel XP tagset. Each table created will go in it's > own > worksheet. > > > (So that's okay, but then there's this:) > > > 2547 ods tagsets.excelxp options(sheet_interval='proc'); > -------- > 22 200 > ERROR 22-322: Syntax error, expecting one of the following: ;, (, ANCHOR, > ARCHIVE, ATTRIBUTES, > AUTHOR, BASE, BODY, CHARSET, CLOSE, CODE, CONTENTS, DATA, > ENCODING, EVENT, > EXCLUDE, FILE, FRAME, GFOOTNOTE, GPATH, GTITLE, HEADTEXT, > METATEXT, NEWFILE, > NOGFOOTNOTE, NOGTITLE, OPERATOR, PAGE, PARAMETERS, PATH, > RECORD_SEPARATOR, > SELECT, SHOW, STYLE, STYLESHEET, TAGSET, TEXT, TRANTAB, > TYPE. > > ERROR 200-322: The symbol is not recognized and will be ignored. > > I've ruled out commenting problems, verified the statement spelling and > syntax against your posts, tried changing the file="" specification to > single quotes, but SAS won't accept the ods tagsets.excelxp options() > statement. Can anyone help? > > TIA, > Steve -- Jack Hamilton Sacramento, California jfh(a)alumni.stanford.org <== Use this, not jfh(a)stanfordalumni.org
From: Mary on 15 Jul 2008 11:48 I don't get an error, but neither do I get the message that you do about "Experimental tagset". ods tagsets.excelxp file="c:\Revised Counts with 9 salelimit&sysdate..xml" style=statistical; ods tagsets.excelxp options(sheet_interval='proc'); ods tagsets.excelxp close; -Mary ----- Original Message ----- From: Steven Raimi To: SAS-L(a)LISTSERV.UGA.EDU Sent: Tuesday, July 15, 2008 10:30 AM Subject: Re: Excelxp Tagsets: is there a way to create multiple sheets on the same file? Thanks Satindra, Jack, Mary, and others on this thread - this addresses a problem I've been working around. However, I've tried to implement this with v9.1.3 under Windows XP, and I've been getting the following error in my log: 2545 ods tagsets.excelxp file="y:\sas\2008 ad hocs\leads sales match\Revised Counts with 9 sale 2545! limit &sysdate..xml" 2546 style=statistical; NOTE: Writing TAGSETS.EXCELXP Body file: y:\sas\2008 ad hocs\leads sales match\Revised Counts with 9 sale limit 14JUL08.xml NOTE: Experimental Excel XP tagset. Each table created will go in it's own worksheet. (So that's okay, but then there's this:) 2547 ods tagsets.excelxp options(sheet_interval='proc'); -------- 22 200 ERROR 22-322: Syntax error, expecting one of the following: ;, (, ANCHOR, ARCHIVE, ATTRIBUTES, AUTHOR, BASE, BODY, CHARSET, CLOSE, CODE, CONTENTS, DATA, ENCODING, EVENT, EXCLUDE, FILE, FRAME, GFOOTNOTE, GPATH, GTITLE, HEADTEXT, METATEXT, NEWFILE, NOGFOOTNOTE, NOGTITLE, OPERATOR, PAGE, PARAMETERS, PATH, RECORD_SEPARATOR, SELECT, SHOW, STYLE, STYLESHEET, TAGSET, TEXT, TRANTAB, TYPE. ERROR 200-322: The symbol is not recognized and will be ignored. I've ruled out commenting problems, verified the statement spelling and syntax against your posts, tried changing the file="" specification to single quotes, but SAS won't accept the ods tagsets.excelxp options() statement. Can anyone help? TIA, Steve
From: Steven Raimi on 15 Jul 2008 11:30 Thanks Satindra, Jack, Mary, and others on this thread - this addresses a problem I've been working around. However, I've tried to implement this with v9.1.3 under Windows XP, and I've been getting the following error in my log: 2545 ods tagsets.excelxp file="y:\sas\2008 ad hocs\leads sales match\Revised Counts with 9 sale 2545! limit &sysdate..xml" 2546 style=statistical; NOTE: Writing TAGSETS.EXCELXP Body file: y:\sas\2008 ad hocs\leads sales match\Revised Counts with 9 sale limit 14JUL08.xml NOTE: Experimental Excel XP tagset. Each table created will go in it's own worksheet. (So that's okay, but then there's this:) 2547 ods tagsets.excelxp options(sheet_interval='proc'); -------- 22 200 ERROR 22-322: Syntax error, expecting one of the following: ;, (, ANCHOR, ARCHIVE, ATTRIBUTES, AUTHOR, BASE, BODY, CHARSET, CLOSE, CODE, CONTENTS, DATA, ENCODING, EVENT, EXCLUDE, FILE, FRAME, GFOOTNOTE, GPATH, GTITLE, HEADTEXT, METATEXT, NEWFILE, NOGFOOTNOTE, NOGTITLE, OPERATOR, PAGE, PARAMETERS, PATH, RECORD_SEPARATOR, SELECT, SHOW, STYLE, STYLESHEET, TAGSET, TEXT, TRANTAB, TYPE. ERROR 200-322: The symbol is not recognized and will be ignored. I've ruled out commenting problems, verified the statement spelling and syntax against your posts, tried changing the file="" specification to single quotes, but SAS won't accept the ods tagsets.excelxp options() statement. Can anyone help? TIA, Steve
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: NLMIXED convergence with simulated ZINB Next: "Uplift" Models |