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