0 users online | 0 Guests and 0 Registered

Does PROC SQL support name ranges in summary functions?


Sadly no.  This is one of those features of the summary functions which allow for a shorthand to identify a group of columns to be processed all of which share a common root, or are positioned together in a table.

In DATA step syntax the follwing are examples of name ranges:

data sumit ;
  set dsn ;
    *** Sum of variables with similar names and consecutively numbered suffices *** ;
    tot_var = sum(of var1-var20) ;
    *** Sum of variables with similar root aka 'begins with' *** ;
   tot_vbl = sum(of vbl:) ;
   *** Sum of variables by position in a table from leftmost to rightmost *** ;
    tot_lst = sum(of firstvar--lastvar) ;
run ;

The PRICEDATA dataset has a number of similarly-named columns:

pricedata metadata

Try running the following:

data sumit ;
set sashelp.pricedata (keep = date price:) ;
totprice = sum(of price:) ;
run ;

None of the shortcuts work in PROC SQL - the SUM function also exists in ANSI Standard SQL, but performs differently.  To sum values acrosss an observation each value must be specified in a comma-separated list.

If it is necessary to use PROC SQL rather than DATA step syntax, it is a bit of a bodge, but it is possible to build lists from the dictionary table and generate macro variables which can then be substituted in the PROC SQL syntax.

proc sql noprint ;
  select  lowcase(name) into :varlist separated by ' ,'
  from dictionary.columns
  where lowcase(libname) = 'sashelp'
  and   lowcase(memname) = 'pricedata'
  and   lowcase(substr(name,1,5)) = 'price'
  ;
quit ;

Which will generate a comma separated list of variables which share the same first five characters.  This can then be substituted into a PROC SQL step:

proc sql noprint ;
  create table sumit as
  select  date
      ,sum(0 ,&varlist) as totprice
  from sashelp.pricedata
  ;
quit ;

To generate a positional name range, it is necessary first to identify the column positions of the leftmost and rightmost columns, then select all the intervening columns.  (It should be noted that for positional ranges all of the columns must be of the same type and appropriate for processing.)

proc sql noprint ;
  select npos into :leftpos
  from dictionary.columns
  where lowcase(libname) = 'sashelp'
  and lowcase(memname) = 'pricedata'
  and lowcase(name) = 'price1'
  ;
  select npos into :rightpos
  from dictionary.columns
  where lowcase(libname) = 'sashelp'
  and lowcase(memname) = 'pricedata'
  and lowcase(name) = 'price17'
  ;
  select lowcase(name) into :varlist separated by ' ,'
  from dictionary.columns
  where lowcase(libname) = 'sashelp'
  and lowcase(memname) = 'pricedata'
  and npos between &leftpos and &rightpos
  ;
quit ;

Which will generate a comma-separated list of all variables based on their position in the table, which can be substituted as before.

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

You can comment this FAQ

Chuck Norris has counted to infinity. Twice.

Records in this category

Tags