topic Re: [SQL] how to exclude a variable when doing 'select' in SQL in SAS Procedures

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

Ken_oy — Wed, 10 Oct 2012 14:40:47 GMT

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 — Wed, 10 Oct 2012 15:54:52 GMT

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 — Thu, 11 Oct 2012 01:57:22 GMT

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 — Thu, 11 Oct 2012 02:11:38 GMT

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

;