POINT= vs DO UNTIL on empty dataset

In an earlier post, Hash Object vs Array Order, I need to populate an array from a data set then use the array to validate values in a SAS data step.  I was able to fill the array using a DO UNTIL construct but this technique failed when the data set was empty.

I had to use the SAS debugger (add the / debug option to the top of the data step) to understand what was going on.  I would have thought this would work since it was my understanding that an UNTIL loop gets evaluated at the bottom of the loop.  However, that was not the case as can be seen when stacking the debugger and log windows.

What did work is using the POINT= option to randomly read a row of data by observation number.  That was the solution that worked for me.

Using SYMGETN() in a view

Have you ever wanted to create a dynamic filter on a SQL based view? The issue is that once you define a macro variable reference, that value as of then gets assigned as part of the view definition. The solution to this issue is to use either the SYMGET() [for character values] or SYMGETN() [for numeric values] functions.

According to the SAS 9.2 Macro Language Reference, SYMGETN() is defined as follows: "In SAS Component Control Language (SCL) programs, returns the value of a global macro variable as a numeric value". As you can see from the below example, this function can also be used very effectively in the Base SAS language.

In the below example, the SAS supplied SASHELP.STOCKS data set is used to retrieve all rows that match a date. If a simple &mydate value is used that will ALWAYS resolve to the value assigned when the view was created. However, using the SYMGETN() function allows for a much more flexible solution.

%let mydate = %sysfunc( putn( '01dec2005'd, 8. ) ) ;

proc sql ;

  create view v_stocks as

    select   *

      from   sashelp.stocks

      where  date = &mydate ;

  describe view v_stocks ;

quit ;

53     describe view v_stocks ;
NOTE: SQL view WORK.V_STOCKS is defined as:
        select *
          from SASHELP.STOCKS
         where date = 16771;

proc sql ;

  create view v_stocks as

    select   *

      from   sashelp.stocks

      where  date = symgetn( 'mydate' ) ;

quit ;

dm "viewtable v_stocks" viewtable ;

Hash Object vs Array Order

I needed to verify that values were in the order specified by a user in a dual selector control object contained in a SAS/AF frame.

My first thought was to load the selected values into a hash object then use a hash iterator object to spin thru the data using its .first() and .next() methods. That worked, but not as I would have expected. The values were all there but they were not in the order entered (natural order) of C, A, D, B but rather in this order: A, C, B, D.

The ordered: 'a' | 'd' (ascending or descending) attribute of the hash declaration would not work in this scenario so I ended up using a one dimensional _temporary_ array. See the below source code and output to better understand what I am describing.

data loadthis ( keep = valx ) ;

  infile datalines eof = alldone ; 

    input valx $ @@ ;

    n + 1 ;

    output ;

  return ;

  alldone:

    call symputx( 'acnt', n ) ;

  return ;

  datalines ;

C A D B 

;

run ;

data out_array 

     out_hiter ;

  if 0 then set loadthis /* prime the PDV */;

  keep valx ;

  /* load hash object and hash iterator object */

  declare hash h(dataset:'loadthis') ;

  declare hiter hi( 'h' ) ;

  h.definekey( 'valx' ) ;

  h.definedone() ;

  /* dimension and populate array */

  array aval[ &acnt ] $ _temporary_ ;

  do until ( eof ) ;

    set loadthis end = eof ;

    rc + 1 ;

    aval[ rc ] = valx ;

  end ;

  /* output array */

  do rc = 1 to &acnt ;

    valx = aval[ rc ] ;

    output out_array ;

  end ;

  /* output hash object and hash iterator object */

  rc = h.output( dataset:'out_hash' ) ;

  rc = hi.first() ;

  do while( rc = 0 ) ;

    output out_hiter ;

    rc = hi.next() ;

  end ;

  stop ;

run ;

Click on image for larger version

PutMacros

While you can use %put _all_ ; to list macro variables to the SAS log, the list is somewhat hard to read and is not in any type of order.  The below %putmacros() macro reads the dictionary.macros table and outputs the contents in ascending macro variable name order.

The output below was created using %put _global_ ; while the table below was generated via the %putmacros() call.


GLOBAL SQLOBS 11
GLOBAL SQLOOPS 55
GLOBAL SYS_SQL_IP_ALL -1
GLOBAL SYS_SQL_IP_STMT
GLOBAL SQLXOBS 0
GLOBAL SQLRC 0
GLOBAL SEX1 F
GLOBAL SQLEXITCODE 0
GLOBAL SEX2 M
GLOBAL AVGWEIGHT1 90.11
GLOBAL AVGWEIGHT2 108.95

<pre>
proc sql noprint ;

  select           sex
                 , avg( weight )
    into           :sex1 - :sex99
                 , :avgweight1 - :avgweight99
from       sashelp.class
group by   sex ;
quit ;
%putmacros()



/****************************************************************
*     Program: putmacros.sas
*      Author: Tom Bellmer
*     Created: 26Jan2011
*     Purpose: writes a sorted list of macro variables
*       Usage: %putmacros( scope= ) ;
*       Notes: Scope value can be ALL, AUTOMATIC, GLOBAL or LOCAL
*    Modified: 
****************************************************************/
%macro putmacros( scope = GLOBAL ) ;
  %local whereclause ;
  %let scope = %upcase( &scope ) ;
  %if &scope = ALL %then %let whereclause = 1 = 1;
  %else %if &scope = LOCAL %then %let whereclause = scope not in ( 'AUTOMATIC', 'GLOBAL' ) ;
  %else %let whereclause = scope = "&scope" ;
  proc sql ;
    create table putmacrosdataset as
      select       name
                 , value
                 , scope length = 32
                 , offset
        from       dictionary.macros
        where      &whereclause
        order by   name 
                 , offset ;
  quit ;
  dm "viewtable putmacrosdataset view=table (label='Macro Variables')" viewtable  ;
%mend ;
/* EOF: putmacros.sas */
</pre>