Range Proc export
in [SAS]
|
From: "Howard Schreier <hs AT dc-sug DOT org>" on 17 Apr 2007 20:26 On Mon, 16 Apr 2007 17:20:59 +0200, baudry <baudry(a)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, > > > > >Alexandre BAUDRY >Assistant chercheur; Projet ADAGE >Unit� d'�conomie rurale (ECRU) >Facult� d'ing�nierie biologique, agronomique et environnementale >Universit� catholique de Louvain (UCL) >Place de la Croix du Sud 2/15 >B-1348 Louvain-La-Neuve >Belgique >e-mail: baudry(a)ecru.ucl.ac.be >Tel(bureau C351): (+32) (0)10/47.87.06 >Tel(secr�tariat): (+32) (0)10/47.36.73 >Fax: (+32) (0)10/47.36.75 ><http://www.ecru.ucl.ac.be/>http://www.ecru.ucl.ac.be Can you provide ready to run code which triggers the problem? It should build both the small and large versions of SMTW.
From: baudry on 18 Apr 2007 04:14 Hi, these procedures should cause a problem. In fact there is an error while= =20 exporting only if the file StatDes.xls already exist before run. /* table creation as example */ data SMTW; length var1 8. var2 8.; do i=3D1 to 10; var1=3D1; var2=3D1; output; end; run; PROC EXPORT DATA=3D SMTW OUTFILE=3D "D:\StatDes.xls" DBMS=3DEXCEL REPLACE; SHEET=3D"specA"; RUN; /* table creation as example */ data SMTW; length var1 8. var2 8. var3 8.; do i=3D1 to 10; var1=3D1; var2=3D1; var3=3D1; output; end; run; PROC EXPORT DATA=3D SMTW OUTFILE=3D "D:\StatDes.xls" DBMS=3DEXCEL REPLACE; SHEET=3D"specA"; RUN; The second exported table is a bit larger than the first one, so an=20 error message occurs and the data previously exported in the excel file are= =20 completely deleted: ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: Too many=20 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= =20 there are more lines. In my opinion, when SAS export data, it creates a=20 range in excel defining the export "area". If the table is larger, the=20 range must be redefined and it causes a problem. Or maybe it is linked to=20 the file _IMEX_.specA.DATA not deleted. Thank you for any help. Best regards, Le 02:26 18/04/2007, vous avez =E9crit: >On Mon, 16 Apr 2007 17:20:59 +0200, baudry <baudry(a)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=3D sgm.SMTW > > OUTFILE=3D "&str.\SGM\Output\StatDes.xls" > > DBMS=3DEXCEL REPLACE; > > SHEET=3D"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.
From: "Howard Schreier <hs AT dc-sug DOT org>" on 19 Apr 2007 09:30 On Wed, 18 Apr 2007 10:14:16 +0200, baudry <baudry(a)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(a)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.
|
Pages: 1 Prev: I need your help! Next: old version of proc logistic mystery... |