[SQL] how to exclude a variable when doing 'select' in SQL

[Ken_oy]

hello

we use (select data.*) to choose those variables we want, but how can we do a selection to choose all variables but to exclude just one variable?

This question came up when i tried to merge two datasets by "id", but I got a warning msg said the "id" had already existed.

Then I have to manually rewrite the 'select' statement.

Any suggestion ? Thanks! se

[Reeza]

I think there might be an option to turn off that warning but I don't recall.

One other method is to run it with the feedback option:

proc sql feedback;

     create table want as

select *

from sashelp.class;

quit;

Get the list from the log and then delete the one(s) you don't want.

[Ksharp]

You can rename it ,then drop it at the output datasets.

proc sql;

create table want(drop=x) as

select a.* , b.b1,b.b2,...., b.id as x

[Tom · Accepted solution]

That would work better using a RENAME dataset option.

create table want(drop=x) as

  select a.*,b.*

  from data1 a

     , data2(rename=(id=x)) b

  where a.id = b.x

;