I disagree - the approach you coded is neat, but requires that an ODBC
source exists for each specific MS Excel file that Stata uses to read
from or write to. That compares quite poorly with SAS's export
procedure.
Stata 9's XMLUSE and XMLSAVE allow convenient data transactions with
the Office 2003 Excel XML format, but they don't permit data output to
more than one worksheet. It would be very neat if StataCorp or one of
you Stata gurus extended XMLSAVE to allow output to multiple Excel XML
worksheets.
Using third-party software packages such as DBMS/COPY and
Stat/Transfer are convenient when dealing with a small number of
datasets; however, they are quite difficult to build into an automated
solution, particularly in a corporate environment where license
limitations mean that they are not installed on every computer running
Stata.
On 12/5/05, Joseph Coveney <[email protected]> wrote:
> Michael Conti wrote:
>
> an additional advantage of SAS is that in can read-in from and output
> data to multiple excel worksheets within an xls file
>
> --------------------------------------------------------------------------------
>
> This readily is doable with Stata, too; see below. Note the limitations (in
> Excel) as pointed out in www.stata.com/support/faqs/data/odbc_excel.html ,
> but SAS has its own foibles as far as reading from and writing to Excel
> workbooks, whether via base procedures, DDE, SAS/Access or ODS, as SAS user
> group proceedings testify.
>
> It seems that statistical software publishers such as SAS Institute and
> StataCorp feel obliged to offer some way for their software packages and
> Excel to interact, to exchange data. This reflects the popularity of the
> leading brand of spreadsheet package in settings where statistical software
> packages are also used. But with the availability of software like
> Stat/Transfer and DBMS/Copy, and with XML arriving on the scene, it seems as
> if it ought to be less important to choose between statistical software
> packages on the basis of their relative abilities to read from and write to
> .xls files.
>
> Joseph Coveney
>
> . clear
>
> . set obs 4
> obs was 0, now 4
>
> . generate patient_id = _n
>
> . generate is_female = mod(_n, 2)
>
> . odbc query Tester
>
> DataSource: Tester
> Path : A:\tester
> -------------------------------------------------------------------------------
> Sheet1$
> Sheet2$
> Sheet3$
> -------------------------------------------------------------------------------
>
> . odbc insert, table(Patients) dsn(Tester) create
>
>
> . clear
>
> . set obs 2
> obs was 0, now 2
>
> . generate is_female = mod(_n, 2)
>
> . generate str sex_name = "Female"
>
> . replace sex_name = "Male" if !is_female
> (1 real change made)
>
> . odbc insert, table(Sexes) dsn(Tester) create
>
>
> . clear
>
> . set obs 2
> obs was 0, now 2
>
> . generate patient_id = _n + 4
>
> . generate is_female = mod(_n, 2)
>
> . odbc insert, table(Patients) dsn(Tester)
>
> . odbc load, exec("SELECT [Patients$].patient_id AS Patient,
> [Sexes$].sex_name AS Sex FROM [Patients$], [Sex
> > es$] WHERE [Patients$].is_female = [Sexes$].is_female") dsn(Tester) clear
>
> . sort Patient
>
> . list, noobs
>
> +------------------+
> | Patient Sex |
> |------------------|
> | 1 Female |
> | 2 Male |
> | 3 Female |
> | 4 Male |
> | 5 Female |
> |------------------|
> | 6 Male |
> +------------------+
>
> .
>
> *
> * For searches and help try:
> * http://www.stata.com/support/faqs/res/findit.html
> * http://www.stata.com/support/statalist/faq
> * http://www.ats.ucla.edu/stat/stata/
>
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
