SQL - Excluding columns from Select

[Ali G]

Is there a way of excluding columns in an SQL select construct? I want
to write a statement that would be synonomous with this :

data table1(keep=var1-var1000);
--data--;
run;

proc sql;
select *
except var1, var2, var3
from table1
;
quit;

I know I could do it with the datastep and DROP= but it's SQL I'm
interested in.

Thanks.

[Toby]

ALI ,

data one ;
infile cards dlm = ' ' ;
input var1 var2 var3 var4 var5 ;
cards ;
1 2 3 4 5
;
run ;

proc sql ;
select *
from one (drop = var4 var5) ;
quit ;


Is there a way of excluding columns in an SQL select construct? I want
to write a statement that would be synonomous with this :


data table1(keep=var1-var1000);
--data--;
run;


proc sql;
select *
except var1, var2, var3
from table1
;
quit;


I know I could do it with the datastep and DROP= but it's SQL I'm
interested in.


Thanks.

[Ali G]

Thanks Toby,

Simple as that...

I tried to extend your code to:

proc sql ;
select *
from one (drop = var4 var5)
where var4 = 4;
quit ;

But it didn't like that.

[Toby]

ALI ,

The Drop statement written like the code below works the same in SQL as
it does in a Data step. It basically stops SAS from reading the
variable into the PDV, thus you can't use a where statement that
references a var in the drop statement.

You can't do the following:

Data one ;
set two (drop = var4) ;
where var4 = 'Alpha' ;
run ;

nor can you do:

proc sql ;
select *
from one (drop = var4)
where var4 = 'Alpha' ;
quit ;

and you can't do the following either:

proc print
data = one (drop = var4) ;
where var4 = 'Alpha' ;
run ;

it looks like you want the printing capabilities of SQL select clause
but also want to mix the var selection capabilities of proc print. If
it were me I would either use proc print or if I needed to do something
that I couldn't do in proc print and could do easily in SQL I would do
something like the following:

proc sql ;
create table _temp as
select *
from one
where var4 = 4 ;

select *
from _temp (drop = Var4 var5) ;
quit ;


Toby Dunn

Thanks Toby,


Simple as that...


I tried to extend your code to:


proc sql ;
select *
from one (drop = var4 var5)
where var4 = 4;
quit ;


But it didn't like that.

[Ken Borowiak]

A warning is generated that data set options will be ignored when used
in the CREATE VIEW statement, except for READ, WRITE, ALTER, PW &
LABEL. Most data set options will work in the CREATE TABLE statement.

Ken

-----Original Message-----
From: Howard Schreier <hs AT dc-sug DOT org> <***@HOWLES.COM>
To: SAS-***@LISTSERV.UGA.EDU
Sent: Thu, 22 Sep 2005 11:30:14 -0400
Subject: Re: SQL - Excluding columns from Select

You'll need two statements, as in (untested);

create view no45 (drop = var4 var5) as
select *
from one
where var4 = 4;

select * from no45;

Post by Ali G
Thanks Toby,
Simple as that...
proc sql ;
select *
from one (drop = var4 var5)
where var4 = 4;
quit ;
But it didn't like that.

[John Hendrickx]

Here's yet another alternative, using macros available at
http://www.datasavantconsulting.com/roland/sasautos.html

proc sql ;
select %remove(%varlist(one),var4,var5)
from one;
quit ;

You can add up to 30 arguments to the %remove macro.

HTH,
John Hendrickx

[Ali G]

That's perfect. A neat solution that does exactly what I want.

Thanks to all who replied.

[s***@gmail.com]

Post by Ali G
That's perfect. A neat solution that does exactly what I want.
Thanks to all who replied.

HI can u give some small example for this .