Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 128
Accepted Solution

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

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


Accepted Solutions
Solution
‎10-10-2012 10:11 PM
Super User
Super User
Posts: 6,687

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

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

;

View solution in original post


All Replies
Super User
Posts: 18,528

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

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.

Super User
Posts: 9,766

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

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

Solution
‎10-10-2012 10:11 PM
Super User
Super User
Posts: 6,687

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

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

;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 12720 views
  • 2 likes
  • 4 in conversation