EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer
Physics
C.S.


SQL ERROR: Subselect has too many fields
SAS Error: attempting to CREATE a DBMS table
Execute: Too many fields defined

SAS Programmer's BookShelf
         
Question: When you tried to use subselect to be more smart/lazy in SQL join, for example, you want to subselect a dataset so it only contains the subset of top ones by some variable. Then you might get this error message: ERROR: Subselect has too many fields.

Answer: The trick is to use another subselect in the sql:
proc sql stimer; 
        connect to netezza (server='server_name' database=database1 
        user=myuser pwd=mypassword connection=global); 
        execute (create table outputfile as 
select a.* from inputdata as a
where vin in (select vin from 
                         (select vin,count(*)  as cnt
                         from inputdata group by 1
                         having cnt >1) 
              as b) /*error showing up if you don't add this subselect*/
distribute on random ) 
by netezza; quit;   

Question: When we try to output SAS datasets to excel via Proc Export procedure, we may get the following error in the log:

        /***************************************************/
        proc export data=sas_data
        outfile='c:\exce_name.xls'
        dbms=excel2007 replace;     /*output to excel-2007 */
        sheet='sheet1'; run;

        proc export data=sas_data
        outfile='c:\exce_name.xlsx'
        dbms=excel replace;             /*output to excel-2010 */
        sheet='sheet1'; run;
        /***************************************************/

ERROR: Error attempting to CREATE a DBMS table.
ERROR: Execute: Too many fields defined.
WARNING: File deletion failed for _IMEX_.secondbigsheet.DATA.
ERROR: Export unsuccessful. See SAS Log for details.

Answer: 1) First we need to double check we use the correct database,

dbms=excel --- .xlsx
dbms=excel2007 --- .xls

2) Note that you don't need to have the excel file ready in your folder before you run this export code, SAS can genenrate the new excel file for you automatically.

In fact, if you have the excel file ready ready in your folder with the tab "tab1", SAS is going to generate another tab called "tab1 (1)" in the same excel file. So if you had some excel template containing some specific format, and you just want to update some tabs, then you cann't use this export procedure to accomplish the task. You can use SAS/DDE to automatically update excel, here is the SAS/DDE tutorial:
How to format Excel file by SAS DDE?

3) Some SAS tutorial mentioned that, it's possibly due to too many columns (more than 255) included in your SAS datasets. This might not always the case. Sometimes you may just have 30 columns and still get the error.

Here is an alternative way to get rid of the error, using keep statement.
        /***************************************************/
        proc export data=sas_data(keep=var1 var2 var3 ... var 30)
        outfile='c:\exce_name.xlsx'
        dbms=excel replace;
        sheet='sheet1'; run;

In case if you don't want to type so many variables names , here is the trick:

        /***************************************************/
        proc contents data=sas_data position
        out=out1; run;

        proc sql;
        select name into: list_of_variables

        separated by ' ' from out1
        where name not in ('variables_not_included'); quit;

        proc export data=sas_data(keep=&list_of_variables.)
        ....

In case if you have more questions, you can submit your questions via Contact us Here, We'll try to answer your questions ASAP.

Related links:

Continue to next: SAS Invisible ERROR in reading raw dataset   SAS tutorial home
Back to: How to generate dummy variables automatically?   Statistics tutorial home