BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mikeyjh
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

5 REPLIES 5
Tom
Super User Tom
Super User

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

Reeza
Super User

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;


Mikeyjh
Calcite | Level 5

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?

Tom
Super User Tom
Super User

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;

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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