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.
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 ...
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 ...
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;
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?
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;
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 |
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.