Excel2007 & SAS 9.1.3 (PROC Export or Import)
in [SAS]
|
Prev: ODS question
Next: About deleting a dataset
From: Nbrcruncher on 9 Oct 2007 17:14 We are currently using Excel2003 with SAS and we would like to move to Excel2007 if there are no major issues with PROC Import or Export. Can anyone attest to having virtually no issues with either of these functions using Excel2007? I would appreciate any comments. Thanks.
From: Alan Churchill on 9 Oct 2007 18:37 I cannot imagine any possible issue you would have. I use infrequently with Excel 2007 and have not had an issue. Excel 2007 reads in Excel 2003 w/o a problem. Since proc export outputs standard Excel workbooks, you should be safe. Alan Alan Churchill Savian www.savian.net -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Nbrcruncher Sent: Tuesday, October 09, 2007 3:15 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Excel2007 & SAS 9.1.3 (PROC Export or Import) We are currently using Excel2003 with SAS and we would like to move to Excel2007 if there are no major issues with PROC Import or Export. Can anyone attest to having virtually no issues with either of these functions using Excel2007? I would appreciate any comments. Thanks.
From: Alan Churchill on 9 Oct 2007 19:20 Caveat time. I only do exporting and not importing into SAS. Excel 2007 uses a completely different file format (zipped XML) than Excel 2003. Hence, in order to import Excel 2007 into SAS you would need to save it as an Excel 2003 format first or save it as something else (tab-delimited). You could probably also set up an OleDb or ODBC connection to it since SAS shouldn't care. Excel 2007, IMO, is worth the hassle. It is an incredible tool and certainly is a strong BI platform. Alan Alan Churchill Savian www.savian.net -----Original Message----- From: Alan Churchill [mailto:savian001(a)gmail.com] Sent: Tuesday, October 09, 2007 4:38 PM To: 'Nbrcruncher'; 'SAS-L(a)LISTSERV.UGA.EDU' Subject: RE: Excel2007 & SAS 9.1.3 (PROC Export or Import) I cannot imagine any possible issue you would have. I use infrequently with Excel 2007 and have not had an issue. Excel 2007 reads in Excel 2003 w/o a problem. Since proc export outputs standard Excel workbooks, you should be safe. Alan Alan Churchill Savian www.savian.net -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Nbrcruncher Sent: Tuesday, October 09, 2007 3:15 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Excel2007 & SAS 9.1.3 (PROC Export or Import) We are currently using Excel2003 with SAS and we would like to move to Excel2007 if there are no major issues with PROC Import or Export. Can anyone attest to having virtually no issues with either of these functions using Excel2007? I would appreciate any comments. Thanks.
From: Reeza on 10 Oct 2007 00:34 On Oct 9, 5:20 pm, savian...(a)GMAIL.COM (Alan Churchill) wrote: > Caveat time. > > I only do exporting and not importing into SAS. Excel 2007 uses a completely > different file format (zipped XML) than Excel 2003. Hence, in order to > import Excel 2007 into SAS you would need to save it as an Excel 2003 format > first or save it as something else (tab-delimited). You could probably also > set up an OleDb or ODBC connection to it since SAS shouldn't care. > > Excel 2007, IMO, is worth the hassle. It is an incredible tool and certainly > is a strong BI platform. > > Alan > > Alan Churchill > Savianwww.savian.net > > -----Original Message----- > From: Alan Churchill [mailto:savian...(a)gmail.com] > Sent: Tuesday, October 09, 2007 4:38 PM > To: 'Nbrcruncher'; 'SA...(a)LISTSERV.UGA.EDU' > Subject: RE: Excel2007 & SAS 9.1.3 (PROC Export or Import) > > I cannot imagine any possible issue you would have. I use infrequently with > Excel 2007 and have not had an issue. Excel 2007 reads in Excel 2003 w/o a > problem. Since proc export outputs standard Excel workbooks, you should be > safe. > > Alan > > Alan Churchill > Savianwww.savian.net > > -----Original Message----- > From: SAS(r) Discussion [mailto:SA...(a)LISTSERV.UGA.EDU] On Behalf Of > Nbrcruncher > Sent: Tuesday, October 09, 2007 3:15 PM > To: SA...(a)LISTSERV.UGA.EDU > Subject: Excel2007 & SAS 9.1.3 (PROC Export or Import) > > We are currently using Excel2003 with SAS and we would like to move to > Excel2007 if there are no major issues with PROC Import or Export. > > Can anyone attest to having virtually no issues with either of these > functions using Excel2007? I would appreciate any comments. Thanks. There are some problems simply because Excel 2007 uses the .xlsx extension which caused me problems. There might be workarounds. Export works fines, import not so well, and to use some of the nice features in 2007 you need to save the workbook as XLSX so you can't just re export your data if something changes. Again there are workarounds, but it's not as straight forward.
From: Alan Churchill on 10 Oct 2007 02:30
Reeza, I wanted to test out my data management tool and what better than a way to convert an Excel 2007 file to a SAS dataset from the command line. The utility can be found here: http://utilities.savian.net/ #24 The utility installs to Program Files/Savian/ConvertExcelToSas. Execute the ConvertExcelToSas with a /? Command to see the usage. You also need the Sas OleDb tool and the SAS Local Provider to make it all happen. All free. I also updated the demo on sascommunity.org of my Savian Data Management tool to illustrate how to do this in code. Alan Alan Churchill Savian www.savian.net -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Reeza Sent: Tuesday, October 09, 2007 10:35 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Re: Excel2007 & SAS 9.1.3 (PROC Export or Import) On Oct 9, 5:20 pm, savian...(a)GMAIL.COM (Alan Churchill) wrote: > Caveat time. > > I only do exporting and not importing into SAS. Excel 2007 uses a completely > different file format (zipped XML) than Excel 2003. Hence, in order to > import Excel 2007 into SAS you would need to save it as an Excel 2003 format > first or save it as something else (tab-delimited). You could probably also > set up an OleDb or ODBC connection to it since SAS shouldn't care. > > Excel 2007, IMO, is worth the hassle. It is an incredible tool and certainly > is a strong BI platform. > > Alan > > Alan Churchill > Savianwww.savian.net > > -----Original Message----- > From: Alan Churchill [mailto:savian...(a)gmail.com] > Sent: Tuesday, October 09, 2007 4:38 PM > To: 'Nbrcruncher'; 'SA...(a)LISTSERV.UGA.EDU' > Subject: RE: Excel2007 & SAS 9.1.3 (PROC Export or Import) > > I cannot imagine any possible issue you would have. I use infrequently with > Excel 2007 and have not had an issue. Excel 2007 reads in Excel 2003 w/o a > problem. Since proc export outputs standard Excel workbooks, you should be > safe. > > Alan > > Alan Churchill > Savianwww.savian.net > > -----Original Message----- > From: SAS(r) Discussion [mailto:SA...(a)LISTSERV.UGA.EDU] On Behalf Of > Nbrcruncher > Sent: Tuesday, October 09, 2007 3:15 PM > To: SA...(a)LISTSERV.UGA.EDU > Subject: Excel2007 & SAS 9.1.3 (PROC Export or Import) > > We are currently using Excel2003 with SAS and we would like to move to > Excel2007 if there are no major issues with PROC Import or Export. > > Can anyone attest to having virtually no issues with either of these > functions using Excel2007? I would appreciate any comments. Thanks. There are some problems simply because Excel 2007 uses the .xlsx extension which caused me problems. There might be workarounds. Export works fines, import not so well, and to use some of the nice features in 2007 you need to save the workbook as XLSX so you can't just re export your data if something changes. Again there are workarounds, but it's not as straight forward. |