Date:         Thu, 19 Apr 2007 09:30:15 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Range Proc export
Content-Type: text/plain; charset=ISO-8859-1

On Wed, 18 Apr 2007 10:14:16 +0200, baudry <baudry@ECRU.UCL.AC.BE> wrote:

>   Hi,
>
>   these procedures should cause a problem. In fact there is an error while
>exporting only if the file StatDes.xls already exist before run.
>
>/* table creation as example */
>data SMTW;
>  length var1 8. var2 8.;
>  do i=1 to 10;
>   var1=1; var2=1; output;
>  end;
>run;
>
>PROC EXPORT DATA= SMTW
>              OUTFILE= "D:\StatDes.xls"
>              DBMS=EXCEL REPLACE;
>       SHEET="specA";
>RUN;
>
>/* table creation as example */
>data SMTW;
>  length var1 8. var2 8. var3 8.;
>  do i=1 to 10;
>   var1=1; var2=1; var3=1; output;
>  end;
>run;
>
>PROC EXPORT DATA= SMTW
>              OUTFILE= "D:\StatDes.xls"
>              DBMS=EXCEL REPLACE;
>       SHEET="specA";
>RUN;
>
>
>   The second exported table is a bit larger than the first one, so an
>error message occurs and the data previously exported in the excel file are
>completely deleted:
>
>ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: Too many
>fields defined..
>WARNING: the file_IMEX_.specA.DATA was not deleted.
>ERROR: Export unsuccessful.  See SAS Log for details.
>(note: the error message is in french and I translated it)
>
>It occurs only when the table has a large number of variables but not when
>there are more lines. In my opinion, when SAS export data, it creates a
>range in excel defining the export "area". If the table is larger, the
>range must be redefined and it causes a problem. Or maybe it is linked to
>the file _IMEX_.specA.DATA not deleted.
>
>Thank you for any help. Best regards,

I get the same error. It seems to occur if the number of variables
decreases, then increases. Here is demo code:

   data smaller; retain var1-var2 0; run;

   data  larger; retain var1-var3 0; run;

   proc export data= larger outfile='c:\temp\test.xls'
    dbms=excel replace;
   sheet="Sheet1";
   run;

   proc export data=smaller outfile='c:\temp\test.xls'
    dbms=excel replace;
   sheet="Sheet1";
   run;

   proc export data= larger outfile='c:\temp\test.xls'
    dbms=excel replace;
   sheet="Sheet1";
   run;

I also notice that if the SHEET statements are removed, the problem goes away.

I would submit this problem to SAS Tech Support.

>
>
>Le 02:26 18/04/2007, vous avez �crit:
>>On Mon, 16 Apr 2007 17:20:59 +0200, baudry <baudry@ECRU.UCL.AC.BE> wrote:
>>
>> >    Dear SAS user's,
>> >
>> >    I have a small question about SAS PROC EXPORT. I looked into the
>> >archives but didn't find something relevant.
>> >
>> >    I want to export results to an excel spreadsheet:
>> >
>> >PROC EXPORT DATA= sgm.SMTW
>> >             OUTFILE= "&str.\SGM\Output\StatDes.xls"
>> >             DBMS=EXCEL REPLACE;
>> >      SHEET="specA";
>> >RUN;
>> >
>> >  The size of the table to export may vary, depending of some options I
>> >put. when I export a small database, then a bigger one, I have an error
>> >message:
>> >
>> >error: Error attempting to CREATE a DBMS table. error: Execute: To many
>> >fields..
>> >warning: file _IMEX_.spec2A.DATA not deleted.
>> >error: Export unsuccessful.  See SAS Log for details.
>> >
>> >    I think it is due to the fact that the export wizard of SAS define a
>> >range in excel which correspond to the export "area" on the excel
>> >spreadsheet. If this area is to small (i.e. a large table following a small
>> >table), there is a problem.
>> >
>> >   I tried switching "Excel" by "Excel2000" or taking out the "Replace",
>> >but nothing changed. I don't want to delete my excel file before each run,
>> >which is an easy solution.
>> >
>> >    So my question is: is it possible to remove or redefine this range in
>> >order to export my data?
>> >
>> >    My SAS version is 9.1.3 on windows.
>> >
>> >
>> >Thank you by advance for any answer. Greetings,
>> >
>> >
>> >
>> >
>>
>>Can you provide ready to run code which triggers the problem? It should
>>build both the small and large versions of SMTW.