Excelxp Tagsets: is there a way to create multiple sheets on
in [SAS]
|
From: Satindra Chakravorty on 10 Jul 2008 12:23 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 >
From: Mary on 10 Jul 2008 12:28 Also note that I did try the suggestion on page 14 here not to close the = tagset, but just to put in a new tagset statement with options of a new = sheet name: ods tagsets.excelxp options(sheet_name=3D'age65to84_nonsmoking'); http://support.sas.com/rnd/base/ods/odsmarkup/p85-30_020205.pdf However, this did not work, because I have intervening ODS output = statements in my program, so it outputted all the little ODS data sets = it created to my excel spreadsheet, which I did not want! Really what = I want is to specify when to reopen the same file without having it = delete the contents, then start writing to it on a new sheet, only doing = a replace if the sheet already exists, but not replacing the whole = workbook. =20 -Mary ----- Original Message -----=20 From: Mary=20 To: SAS-L(a)LISTSERV.UGA.EDU=20 Sent: Thursday, July 10, 2008 10:25 AM Subject: Excelxp Tagsets: is there a way to create multiple sheets on = the same file? Question on ODS Excel tagsets: I'm doing this code: ods tagsets.excelxp = file=3D'C:\Work_Activities\crosstabs_results_14.xls'=20 style=3Dstatistical options(sheet_name=3D'age12vs11_all'); run; proc print noobs data=3Dresults; run; ods tagsets.excelxp close; run; But I find if I put in the same filename with a different sheet, it = deletes=20 the previous file instead of adding a sheet to it. Is there a way to = get=20 it to add sheets to an existing file rather so I don't have to put a=20 different file name down for each sheet that I want? -Mary=20
From: Mary on 10 Jul 2008 12:32 Satindra, Will this work even if I have intervening ODS OUTPUT statements in my = program? I just tried the sheet name only (without the sheet_interval) = but got sheets every time I specified an ODS OUTPUT file. =20 -Mary ----- Original Message -----=20 From: Satindra Chakravorty=20 To: SAS-L(a)LISTSERV.UGA.EDU=20 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=3D'...' = options(sheet_interval=3D'proc'); and a second line that goes something like this... ods tagsets.excelxp options(sheet_name=3D'S1'); PROC ....; run; ods tagsets.excelxp options(sheet_name=3D'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=3D'C:\Work_Activities\crosstabs_results_14.xls' > style=3Dstatistical > options(sheet_name=3D'age12vs11_all'); > run; > proc print noobs data=3Dresults; > 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 >
From: Mary on 10 Jul 2008 12:55 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!). =20 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 -----=20 From: Satindra Chakravorty=20 To: Mary=20 Cc: SAS-L(a)listserv.uga.edu=20 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=3D'...' = options(sheet_interval=3D'proc'); and a second line that goes something like this... ods tagsets.excelxp options(sheet_name=3D'S1'); PROC ....; run; ods tagsets.excelxp options(sheet_name=3D'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=3D'C:\Work_Activities\crosstabs_results_14.xls' > style=3Dstatistical > options(sheet_name=3D'age12vs11_all'); > run; > proc print noobs data=3Dresults; > 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 >
From: yzg9 on 10 Jul 2008 12:28
Mary, Ahh, the wonders of ExcelXP await you! Two methods may be helpful, one I'm always doing, the other I haven't tried. 1) Create all of your result datasets, then: >>ods tagsets.excelxp file='C:\Work_Activities\crosstabs_results_14.xls' >>style=statistical >>options(sheet_name='age12vs11_all_1'); >>proc print noobs data=results1; >>run; >>options(sheet_name='age12vs11_all_2'); >>proc print noobs data=results2; >>run; >>options(sheet_name='age12vs11_all_3'); >>proc print noobs data=results3; >>run; >>ods tagsets.excelxp close; >>run; 2) Look into using the Excel libname engine. You can select the Excel cells that need to be updated, and use a data step to do the updating. This is a bit simplified, sorry. This one I haven't done, but read about it in one of the recent papers on this topic. Let me find it... Paul Choate's SUGI31 paper "DeMystifying SAS Libname Engine in MSExcel" was one I read. John Gerstle MS Applied Neuroscience, MS Applied Statistics Biostatistician Northrop Grumman CDC Information Technological Support Contract (CITS) NCHSTP \DHAP \HICSB \Research and Dissemination Team Centers for Disease Control and Prevention Phone: 404-639-3980 Fax: 404-639-2980 yzg9 at cdc dot gov >>-----Original Message----- >>From: owner-sas-l(a)listserv.uga.edu [mailto:owner-sas-l(a)listserv.uga.edu] >>On Behalf Of Mary >>Sent: Thursday, July 10, 2008 11:26 AM >>To: SAS-L(a)LISTSERV.UGA.EDU >>Subject: Excelxp Tagsets: is there a way to create multiple sheets on the >>same file? >> >>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 |