topic Re: SQL insert error in SAS Procedures

SQL insert error

Mikeyjh — Thu, 16 May 2013 19:40:21 GMT

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.

Re: SQL insert error

Tom — Thu, 16 May 2013 19:50:49 GMT

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 ...

Re: SQL insert error

Reeza — Thu, 16 May 2013 19:55:27 GMT

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;

Re: SQL insert error

Mikeyjh — Thu, 16 May 2013 20:07:08 GMT

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?

Re: SQL insert error

Tom — Thu, 16 May 2013 20:22:32 GMT

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;

Re: SQL insert error

Reeza — Thu, 16 May 2013 22:01:35 GMT

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

Variable

Type

Len

3	Age	Num	8
1	Name	Char	8
2	Sex	Char	1
4	Weight	Num	8