Help using Base SAS procedures

SQL insert error

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

SQL insert error

Hi,

Can anyone please help with the following. I'm trying to run the code below but it produces the error given underneath.

proc sql;

     create table except_PE (keep= usubjid VISIT VISITNUM VISITDAT PETEST PEORRES PEABN)   

     like updated.PE;

     insert into except_PE

     select       

     usubjid, VISIT, VISITNUM, VISITDAT, PETEST, PEORRES, PEABN   

     from updated.PE   

     where usubjid="0001"   

     except

     select       

     usubjid, VISIT, VISITNUM, VISITDAT, PETEST, PEORRES, PEABN   

     from olddata.PE

     where usubjid="0001";

quit;


ERROR: Value 3 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

ERROR: Value 4 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.


I've checked and the types do match so if anyone can provide any pointers that would be great. Thanks.


Accepted Solutions
Solution
‎05-16-2013 03:50 PM
Super User
Super User
Posts: 6,500

Re: SQL insert error

Error is because the order in the dataset is different than the order in your SELECT statement.

You could fix it by listing the column you want to insert into in your INSERT statement.

insert into except_PE (usubjid, VISIT, VISITNUM, VISITDAT, PETEST, PEORRES, PEABN)

     select      

     usubjid, VISIT, VISITNUM, VISITDAT, PETEST, PEORRES, PEABN 


Or you could do it all in one statement.  Not sure what value is added by doing the two step CREATE/INSERT rather than a single step of CREATE ... AS SELECT ...

View solution in original post


All Replies
Solution
‎05-16-2013 03:50 PM
Super User
Super User
Posts: 6,500

Re: SQL insert error

Error is because the order in the dataset is different than the order in your SELECT statement.

You could fix it by listing the column you want to insert into in your INSERT statement.

insert into except_PE (usubjid, VISIT, VISITNUM, VISITDAT, PETEST, PEORRES, PEABN)

     select      

     usubjid, VISIT, VISITNUM, VISITDAT, PETEST, PEORRES, PEABN 


Or you could do it all in one statement.  Not sure what value is added by doing the two step CREATE/INSERT rather than a single step of CREATE ... AS SELECT ...

Super User
Posts: 17,828

Re: SQL insert error

Even though you've specified that order in keep, if they weren't in that order in the original dataset they won't be in that order in the SQL table.

proc sql;

     create table except_PE (keep= usubjid VISIT VISITNUM VISITDAT PETEST PEORRES PEABN)   

     like updated.PE;

quit;

proc contents data=except_pe;

run;


Contributor
Posts: 28

Re: SQL insert error

Thanks for your comments. The benefit of the "create table like" statement I thought would be that the new table would have the same attributes (labels, formats ...) as the original table. Is that not correct?

Super User
Super User
Posts: 6,500

Re: SQL insert error

It should keep the formats and labels from the original field.

Try a test:

data x; set sashelp.class; run;

proc sql noprint ;

create table y as select * from sashelp.class;

quit;

proc compare data=x compare=y;

run;

Super User
Posts: 17,828

Re: SQL insert error

Yes. It also keeps the original variable order, the KEEP statement does not change the variable order.

If you run the following for example and look at the variable order, sex is first in the keep but #2 in the dataset.

proc sql;

    create table test (Keep=sex age weight name)

    like sashelp.class;

quit;

proc contents data=test;

run;

    Alphabetic List of Variables and Attributes

                    #VariableTypeLen

                    3Age     Num   8
                    1Name    Char  8
                    2Sex     Char  1
                    4Weight  Num   8
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1995 views
  • 0 likes
  • 3 in conversation