ODS CSV
in [SAS]
|
Prev: Proc Plan?
Next: different dates formats
From: SUBSCRIBE SAS-L Dan on 16 Feb 2010 12:10 Thanks guys for the quick helps. I will try them later on. Dan
From: Mike Rhoads on 16 Feb 2010 13:22 John, That's a good point. The only downside (and probably the reason many people make the extension XLS) is that if you double-click on an XML file in Windows Explorer, it will most likely open by default in some application other than Excel, such as your default Web browser. (You can change the default in Explorer, of course.) I got in the habit of using XLS back in the Office 2002 days, when Excel would open these files without complaint (or without warning, depending on your point of view). Perhaps I should start going the other way. Mike Rhoads RhoadsM1(a)Westat.com -----Original Message----- From: Hixon, John [mailto:jhixon(a)amgen.com] Sent: Tuesday, February 16, 2010 1:17 PM To: SAS(r) Discussion; Mike Rhoads Subject: Re: ODS CSV Mike wrote (in part) >From: Mike Rhoads <RHOADSM1(a)WESTAT.COM> >So what you really want is a way to get your data into Excel? >CSV and CSVALL can do that in a basic manner, but it won't let you get very fancy. >It certainly won't let you merge cells in Excel, which is what it >sounds like you are trying for. >I'd recommend using the ExcelXP tagset. >Note that you are fooling Excel by using an XLS file extension. >You're not actually creating a binary XLS file, but rather an XML file >in SpreadsheetML format, that can be opened by ExcelXP and later versions. >In Office 2007, at least, you may get a message asking you to confirm >you want to open the file, because it recognizes the deception. >Just use something like: >ods tagsets.excelxp file='c:\junk\Test.xls' options(embedded_titles='Yes') style=Minimal; ... >ods tagsets.excelxp close; Mike: I would suggest using the .xml extension in the output file name. Then, Excel will open it without complaining about the 'deception'. Cheers, John Hixon jhixon(a)amgen.com
From: "Hixon, John" on 16 Feb 2010 13:16 Mike wrote (in part) >From: Mike Rhoads <RHOADSM1(a)WESTAT.COM> >So what you really want is a way to get your data into Excel? >CSV and CSVALL can do that in a basic manner, but it won't let you get very fancy. >It certainly won't let you merge cells in Excel, which is what it >sounds like you are trying for. >I'd recommend using the ExcelXP tagset. >Note that you are fooling Excel by using an XLS file extension. >You're not actually creating a binary XLS file, but rather an XML file in >SpreadsheetML format, that can be opened by ExcelXP and later versions. >In Office 2007, at least, you may get a message asking you to confirm >you want to open the file, because it recognizes the deception. >Just use something like: >ods tagsets.excelxp file='c:\junk\Test.xls' options(embedded_titles='Yes') style=Minimal; ... >ods tagsets.excelxp close; Mike: I would suggest using the .xml extension in the output file name. Then, Excel will open it without complaining about the 'deception'. Cheers, John Hixon jhixon(a)amgen.com
From: Joe Matise on 16 Feb 2010 13:27 I think starting with Office XP (certainly Office 2007) Excel became the default opening tool for .xml files that are formatted in OfficeXML [or whatever the OfficeXP format is - might have that name wrong]. I'm guessing this line near the top: <?mso-application progid="Excel.Sheet"?> instructs Windows to open it in Excel. Works on most-but-not-all PCs that I've interacted with; some running very old version of office (2003 or before) have problems with it, although they're also running Windows 2000 so that could be related. -Joe On Tue, Feb 16, 2010 at 12:22 PM, Mike Rhoads <RHOADSM1(a)westat.com> wrote: > John, > > That's a good point. The only downside (and probably the reason many > people make the extension XLS) is that if you double-click on an XML file in > Windows Explorer, it will most likely open by default in some application > other than Excel, such as your default Web browser. (You can change the > default in Explorer, of course.) > > I got in the habit of using XLS back in the Office 2002 days, when Excel > would open these files without complaint (or without warning, depending on > your point of view). Perhaps I should start going the other way. > > > Mike Rhoads > RhoadsM1(a)Westat.com > > > > -----Original Message----- > From: Hixon, John [mailto:jhixon(a)amgen.com] > Sent: Tuesday, February 16, 2010 1:17 PM > To: SAS(r) Discussion; Mike Rhoads > Subject: Re: ODS CSV > > Mike wrote (in part) > > >From: Mike Rhoads <RHOADSM1(a)WESTAT.COM> > > >So what you really want is a way to get your data into Excel? > > >CSV and CSVALL can do that in a basic manner, but it won't let you get > very fancy. > >It certainly won't let you merge cells in Excel, which is what it > >sounds like you are trying for. > > >I'd recommend using the ExcelXP tagset. > >Note that you are fooling Excel by using an XLS file extension. > >You're not actually creating a binary XLS file, but rather an XML file > >in SpreadsheetML format, that can be opened by ExcelXP and later versions. > >In Office 2007, at least, you may get a message asking you to confirm > >you want to open the file, because it recognizes the deception. > > >Just use something like: > > >ods tagsets.excelxp file='c:\junk\Test.xls' > options(embedded_titles='Yes') style=Minimal; ... > >ods tagsets.excelxp close; > > Mike: I would suggest using the .xml extension in the output file name. > Then, Excel will open it without complaining about the 'deception'. > > Cheers, > > John Hixon > jhixon(a)amgen.com >
From: Mike Rhoads on 16 Feb 2010 13:47
A quick experiment on my part bears out Joe's comment. Plus I just received a reply from a well-placed birdie recommending that you go with XML for the extension. Trying to be tricky and use XLS can apparently cause other problems as well. So, go with XML! Mike Rhoads RhoadsM1(a)Westat.com -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Joe Matise Sent: Tuesday, February 16, 2010 1:27 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Re: ODS CSV I think starting with Office XP (certainly Office 2007) Excel became the default opening tool for .xml files that are formatted in OfficeXML [or whatever the OfficeXP format is - might have that name wrong]. I'm guessing this line near the top: <?mso-application progid="Excel.Sheet"?> instructs Windows to open it in Excel. Works on most-but-not-all PCs that I've interacted with; some running very old version of office (2003 or before) have problems with it, although they're also running Windows 2000 so that could be related. -Joe On Tue, Feb 16, 2010 at 12:22 PM, Mike Rhoads <RHOADSM1(a)westat.com> wrote: > John, > > That's a good point. The only downside (and probably the reason many > people make the extension XLS) is that if you double-click on an XML > file in Windows Explorer, it will most likely open by default in some > application other than Excel, such as your default Web browser. (You > can change the default in Explorer, of course.) > > I got in the habit of using XLS back in the Office 2002 days, when > Excel would open these files without complaint (or without warning, > depending on your point of view). Perhaps I should start going the other way. > > > Mike Rhoads > RhoadsM1(a)Westat.com > > > > -----Original Message----- > From: Hixon, John [mailto:jhixon(a)amgen.com] > Sent: Tuesday, February 16, 2010 1:17 PM > To: SAS(r) Discussion; Mike Rhoads > Subject: Re: ODS CSV > > Mike wrote (in part) > > >From: Mike Rhoads <RHOADSM1(a)WESTAT.COM> > > >So what you really want is a way to get your data into Excel? > > >CSV and CSVALL can do that in a basic manner, but it won't let you > >get > very fancy. > >It certainly won't let you merge cells in Excel, which is what it > >sounds like you are trying for. > > >I'd recommend using the ExcelXP tagset. > >Note that you are fooling Excel by using an XLS file extension. > >You're not actually creating a binary XLS file, but rather an XML > >file in SpreadsheetML format, that can be opened by ExcelXP and later versions. > >In Office 2007, at least, you may get a message asking you to confirm > >you want to open the file, because it recognizes the deception. > > >Just use something like: > > >ods tagsets.excelxp file='c:\junk\Test.xls' > options(embedded_titles='Yes') style=Minimal; ... > >ods tagsets.excelxp close; > > Mike: I would suggest using the .xml extension in the output file name. > Then, Excel will open it without complaining about the 'deception'. > > Cheers, > > John Hixon > jhixon(a)amgen.com > |