99 users online | 99 Guests and 0 Registered

Is it possible to generate a shorthand SELECT Clause in PROC SQL with every column 'except' those listed?

No.  Although there is the facility to return all columns using the SELECT * syntax, there is no equivalent for the DROP = dataset option on the output data / report.

The attached utility macro can be used however to generate a SELECT clause and store this in a macro variable.  The macro is self-documenting; to understand the parameters call the macro:


which returns the following in the LOG:

NOTE: There are five parameters passed to this macro:
      ds        = [Specify a one- or a two-level dataset name from which variables are SELECTed]
      tbl_alias = [Specify a table alias.  If none is specified the table name will be used.]
      not_these = [Specify the variables to be excluded from the SELECT list.]
      keepmac   = [Specify a macro variable name for the SELECT list.  If not specified
                   the default name will be KEEP_THESE.]
      case      = [Specify U | L | I to return the SELECT clause in Upcase | Lowcase | Internal]


The ds parameter accepts a one- or two-level dataset name.  The macro issues an ERRROR if the dataset specified does not exist.  If the parameter is not specified, the macro will process the last-created dataset as specified in SYSLAST.

The tbl_alias parameter allows a table alias to be specified, otherwise the table name will be used.

The not_these parameter allows a list of columns to be excluded from the SELECT clause.  Columns may be specified with or without a table alias.  If the parameter is not specified the macro issues a NOTE advising that all variables will be included on the SELECT clause.

The keepmac parameter allows the variable to be used to store the SELECT clause to be named.  If not specified, the results will be stored in a Global Macro variable called keep_these.

The case parameter allows the SELECT clause to be built to match a 'house' style: returning in (U)pper case, (L)ower case or (I)nternal (i.e. as stored).

A macro call such as:

%all_except(ds = sashelp.cars, tbl_alias = c, not_these = "c.invoice, MPG_Highway")

%put &keep_these ;

generates a macro variable keep_these contaning the following:

c.make ,c.model ,c.type ,c.origin ,c.drivetrain ,c.msrp ,c.enginesize ,c.cylinders ,c.horsepower ,c.mpg_city ,c.weight ,c.wheelbase ,c.length

Attached files: all_except.sas

Alan D Rudland
Average rating:0 (0 Votes)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.