DOW Sort

Sorting data in ascending or descending order is a very easy and straightforward process.  However, there are times when a simple sort does not suffice.  Consider a scenario of submitting a large number of processes in a SAS grid environment that has a limit on the number of active parallel processes.

In that scenario, you will want to mix tasks that take a long time with those quicker running tasks.  There is normally a key driver that can be used to identify longer running processes.  The number of rows and columns are prime candidates to sort the data.

This example uses the available SASHELP.CLASS data set and sorts it by age and height in ascending order.  The ability to read SAS data sets in random order via the POINT= option in a SET statement make this relatively easy.  The idea is to read the highest or last row with the first X rows that are lower in value.  The technique used is a variant of the DOW Loop created and popularized by SAS Gurus Don Henderson, Paul Dorfman and Ian Whitlock.

The below data set is sorted by age and height.

The following data set shows the result of reading the last row then the top 5 rows.  After that the second to the last row is output followed by rows 6 through 10

/*******************************************************************************

     Program: sort_dow.sas

      Author: Tom Bellmer

     Created: 12/07/2020 @ 1:25:37 PM

SAS Version: SAS 9.4 (TS1M6)

          OS: LIN X64

     Purpose: Places subrows under the highest value via a DOW loop

       Notes: Assumes the input data set is sorted in ascending order

              will be used to stuff low value rows below high for RSUBMITS

              to reduce expected time in each parallel session

 

                             Modifications in descending order

FL-YYYYMMDD                             Description

----------- --------------------------------------------------------------------

 

         1    1    2    2    3    3    4    4    5    5    6    6    7    7    8

....5....0....5....0....5....0....5....0....5....0....5....0....5....0....5....0

*******************************************************************************/

 

%macro sort_dow(

    dsnin   =

  , subrows = 5

  , dsnout  = sort_dow

);

 

  %if not %sysfunc(exist(&dsnin)) %then %do;

    %put %str(E)RROR: Invalid input data set: &dsnin..;

    %return;

  %end;

 

  data &dsnout;

    if 0 then set &dsnin nobs = totobs;

 

    do until(totobs = i);

      set &dsnin point = totobs;

      totobs = totobs - 1;

      output;

      if totobs = i then stop;

 

      do _n_ = 1 to &subrows;

        i + 1;

        set &dsnin point = i;

        output;

        if totobs = i then stop;

      end;

    end;

    stop;

  run;

%mend;

 

/*EOF: sort_dow.sas */

 

 

 

proc sort data = sashelp.class out = class;

  by age height;

run;

 

%sort_dow(

    dsnin   = class

  , subrows = 5

  , dsnout  = sort_dow

);

Data Driven Report using the VVALUEX function

The SAS VVALUEX function can be used to display the contents of a variable based on another variable. VVALUEX is somewhat similar to a SAS macro variable but it will create a 200 byte character variable unless the variable has been previously defined with a length or attrib statement. This really comes into play when data driving a program to change a column's format or conditionally display an image as is done here. Be sure to click on the images to see larger versions.

The cardata data set contains raw data collected from various sources on the fastest automobile for each of the Big 3 auto makers. The below image reveals the contents of this data set.

The carmetadata table is used to define the label and format to be used along with the name of the variable to be resolved. A tilde (~) is being used as the ODS escapechar - adding the underscore (_) simulates a non breaking space which is how the 4 character indentation works. Here is what that looks like:

A data step is used to read in the carmetadata values and then look up the corresponding values in the cardata table using the vvaluex function. Each of the rows in the cardata table is read using the SET POINT= random access technique. I used point= over the more popular do until( eof ) via the set end= option so did not have to reset the end= variable for each loop. Here is what the final cars data set looks like.

You can read the below code to see how all this works and view the final rendered PDF file here.

data cardata ;
  input name : $16. hp zeroto60 mpgcity mpghighway topspeed 
        msrp weight qtrmiletime qtrmilespeed ;
  datalines ;
  Mustang 662 3.5 15 24 189 55935 3850 11.6 126
  Corvette 650 2.95 15 22 200 78995 3524 10.95 127
  Challenger 707 3.6 13 21 199 63980 4439 10.8 126
  ;
run ;

data carmetadata ;
  infile datalines truncover ;
  input id measure & $32. format : $16. var : $32. ;
  datalines ;
   1 
   2 Horsepower  comma9. hp 
   3 Curb Weight  comma9.  weight 
   4 Zero To 60  8.2 zeroto60
   5 Quarter Mile  $32.
   6 ~_~_~_~_Time  8.2 qtrmiletime
   7 ~_~_~_~_Speed  comma9. qtrmilespeed 
   8 Miles Per Gallon  $32.   
   9 ~_~_~_~_City  comma9. mpgcity   
  10 ~_~_~_~_Highway  comma9. mpghighway 
  11 Top Speed  comma9. topspeed   
  12 Retail Price  dollar9. msrp    
  ;
run ;

data cars( keep = format measure corvette challenger mustang ) ;
  attrib
    corvette   length = 8 label = 'Chevrolet Corvette Z06'
    challenger length = 8 label = 'Dodge Challenger SRT Hellcat'
    mustang    length = 8 label = 'Ford Mustang Shebly GT500' ;
    
  set carmetadata ;
  if not missing( var ) then do ;
    do i = 1 to totalobs ;
      set cardata point = i nobs = totalobs  ;
           if name='Corvette'   then corvette   = input(vvaluex(var), best.) ;
      else if name='Challenger' then challenger = input(vvaluex(var), best.) ;
      else if name='Mustang'    then mustang    = input(vvaluex(var), best.) ;
    end ;
  end ;
run ;

options nodate nonumber missing = '' ;
ods escapechar = "~" ;
ods listing close ;
ods pdf file = "%sysfunc(pathname(work))\cars.pdf" style=sasweb startpage=no ;
title "Fastest American Production Cars by Manufacturer" ;

proc report data = cars ;
  column format measure challenger corvette mustang ;
  define format     / noprint ;
  define measure    / display '' ;
  define challenger / display ;
  define corvette   / display ;
  define mustang    / display ;
  compute corvette ;
    if missing( measure ) and missing( corvette ) then call define( _col_, 
      'style', 'style=[preimage="c:\temp\corvette500.png"]') ;
    else call define( _col_, 'format', format ) ;
  endcomp ;
  compute challenger ;
    if missing( measure ) and missing( challenger ) then call define( _col_, 
      'style', 'style=[preimage="c:\temp\challenger500.png"]') ;
    else call define( _col_, 'format', format ) ;
  endcomp ;
  compute mustang ;
    if missing( measure ) and missing( mustang ) then call define( _col_, 
      'style', 'style=[preimage="c:\temp\mustang500.png"]') ;
    else call define( _col_, 'format', format ) ;
  endcomp ;
run ;

title "Performance Results" ;
proc sgplot data = cardata ;
  hbar name / 
    response = qtrmiletime 
    legendlabel = 'Quarter Mile' 
    stat        = mean 
    datalabel  
    dataskin    = gloss
    fillattrs   = graphdatadefault
  ;
  hbar name / 
    response    = zeroto60 
    legendlabel = 'Zero to 60mph' 
    stat        = mean 
    datalabel 
    barwidth    = 0.6  
    dataskin    = gloss
    fillattrs   = ( color = cx7c95ca )
  ;
  xaxis label   = 'Seconds' ;
  yaxis display = ( nolabel ) ;
run ;

ods pdf close ;
ods listing ;

LagLead Macro

UPDATE: If you have SAS/ETS installed, you can use proc expand as an alternative. There are a ton of options with this procedure but here is the syntax to emulate the below code:

proc expand data = sashelp.class out = example method = none ;
  convert age = sumage / transform = ( cmovsum 5 trimleft 2 trimright 2 ) ;
run ;

The SAS supplied LAG() function is used to retain previously read variables. There is no supplied LEAD function so the below lagLead function was created.

The key to making this work is to LINK to a DO LOOP that utilizes the POINT= option on the SET statement to read the data in random access mode. A RENAME= option was required to not overwrite the existing VAR of interest in the program data vector (PDV).

In the below example, while sitting on row 5 the prior two, current row and next two rows are summed resulting in a value of 65 ( 13 + 14 + 14 + 12 + 12 ).

/**************************************************************************
*     Program: lagLead.sas
*      Author: Tom Bellmer
*     Created: 05NOV2014 
*     Purpose: read and sum X rows before and after current row
**************************************************************************/

%macro lagLead( 
    dsn      = sashelp.class
  , outdsn   = example
  , var      = age
  , outvar   = sumage
  , lag      = 2
  , lead     = 2 
) ;

  %local tempvar ;
  %let tempvar = temp_%sysfunc(ceil( %sysfunc( ranuni(0) ) * 10000 ) ) ;

  data &outdsn.( drop = &tempvar. ) ;
    set &dsn. nobs = totalrows ;
      if _n_ > &lag. and _n_ <= ( totalrows - &lead. ) then link laglead ;
      output ;
    return ;

    laglead:
      do i = ( _n_ - &lag. ) to ( _n_ + &lead. ) ;
        set &dsn.( keep = &var. rename = ( &var. = &tempvar. ) ) point = i ;
        &outvar. = sum( &outvar., &tempvar. ) ;
      end ;
    return ;
  run ;
%mend ;

/* EOF: lagLead.sas  */