0 users online | 0 Guests and 0 Registered

Can I exclude certain columns from the select * clause?


The simple answer is no: there is no SQL equivalent to the DROP= dataset option on a SET or MERGE statement to exclude only a few columns from a long list.

It is possible however to use some SQL syntax to easily build the desired SELECT clause.

Using SELECT * will by default select 'all' columns, but using it in conjunction with the FEEDBACK option it causes the system to expand the clause to its full extent, and writes the resulting syntax in the LOG. e.g. consider the code:

proc sql feedback noprint ;
  select * from sashelp.cars ;
quit ;

generates the following in the LOG:

1    proc sql feedback noprint ;
2      select * from sashelp.cars ;
NOTE: Statement transforms to:

        select CARS.Make, CARS.Model, CARS.Type, CARS.Origin, CARS.DriveTrain, CARS.MSRP, CARS.Invoice, CARS.EngineSize, CARS.Cylinders, CARS.Horsepower, CARS.MPG_City, CARS.MPG_Highway, CARS.Weight, CARS.Wheelbase, CARS.Length
          from SASHELP.CARS;

3    quit ;

It is possible then to copy and paste this SELECT clause back into the editor and delete the columns which are not required.

proc sql feedback ;
  select CARS.Make, CARS.Model, CARS.Type, CARS.Origin, CARS.DriveTrain, CARS.MSRP, CARS.Invoice, CARS.EngineSize, CARS.Cylinders, CARS.Horsepower, CARS.MPG_City, CARS.MPG_Highway, CARS.Weight, CARS.Wheelbase, CARS.Length
  from sashelp.cars ;
quit ;

When the system expands the SELECT * clause it adds a default table alias of the table name.  If the intention is to insert the full SELECT clause into existing code which already uses a table alias, then this can also be achieved by using the same table alias in the FEEDBACK step:

proc sql feedback noprint ;
  select * from sashelp.cars as c ;
quit ;

Which generates the following in the LOG:

select C.Make, C.Model, C.Type, C.Origin, C.DriveTrain, C.MSRP, C.Invoice, C.EngineSize, C.Cylinders, C.Horsepower, C.MPG_City, C.MPG_Highway, C.Weight, C.Wheelbase, C.Length

 

Author:
Alan D Rudland
Revision:
1.3
Average rating:0 (0 Votes)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.

Records in this category

Tags