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 ;