Sample 37005: Subsetting data based on a selected cell in the SAS/AF® Table Viewer
 |  |  |  |  |
Overview
The SAS/AF Table Viewer has several options for applying a subset to the displayed data. Users can access the Where Builder window from the menus, the right-mouse button, and the command line. Programmers can program their own windows to supply subsetting functionality, or apply a conditional clause using Source Control Language or SCL.
Sometimes it would be simpler to just select a value from the display and apply the subset based on that value. SAS/AF programmers can accomplish this with a few additional methods.
Overriding the Table Viewer's _popup method allows the programmer to customize the existing method with new menu options. In this case, three new actions are added to the standard menu:
- Apply new subset
- Append to subset
- Clear subset
Defining the New Actions
The first action creates and applies a WHERE clause to the data set based on the data in the selected cell. The second action extends the existing WHERE clause by adding a new conditional operation based on the data in the selected cell. The final action removes any existing WHERE clause applied to the table. This final action is functionally equivalent to the "WHERE Clear" action already present in the menu, and could be removed if the programmer prefers.
The code to customize the pop-up menu is:
/*******************************************************
* Overrides the default pop menu. Three new functions *
* are added to the menu: Applying a subset, Appending *
* to an existing subset, and Clearing the subset. *
* Item 1 in the list is a line break. *
* Item 2 allows the user to define a subset based on *
* the cell that was clicked. *
* Item 3 allows the user to add the selected cell to *
* the existing subset. *
* Item 4 clears the existing subset and allows the *
* display of all the data. *
* Item 5 is a closing line break. *
*******************************************************/
popup:
method plist:input:num
sel:input:num;
dcl list where_l = {};
rc=insertc(plist,'',-1);
rc=insertc(plist,'Apply new subset',-1);
rc=insertc(plist,'Append to subset',-1);
rc=insertc(plist,'Clear subset',-1);
rc=insertc(plist,'',-1);
call super(_self_,'_popup',plist,sel);
select(sel);
when(2)
_self_.buildWhereClause(where_l);
when(3)
_self_.appendToWhereClause(where_l);
otherwise;
end;
dcl object modelid = getnitemn(_self_, '_modelid_');
modelid._setWhere(where_l);
where_l = dellist(where_l);
endmethod;
|
Defining the Methods
After defining the new actions, the next step is to define the methods that will be performed by the actions. In this case, two new methods are going to be implemented as an extension of the Table Viewer control. These methods are: buildWhereClause(L) and appendToWhereClause(L). Both methods accept a list containing the current WHERE clause contents. When the methods are called by the above actions, this list is empty. In other cases, the list might contain previously applied WHERE clauses.
The appendToWhereClause method is the simplest method to implement. It retrieves any existing WHERE clause from the model that references the data set, and passes that WHERE clause as a list to the buildWhereClause method.
/*******************************************************
* The appendToWhereClause retrieves the current where *
* clause and passes it to the buildWhereClause method.*
*******************************************************/
appendToWhereClause:
method where_l:update:list;
dcl object modelid = getnitemn(_self_, '_modelid_');
modelid._getWhere(where_l);
_self_.buildWhereClause(where_l);
endmethod;
|
The buildWhereClause method is more complicated. This method must determine which cell the user selected, the data set variable that is represented by the cell's column, and the value that is represented by the cell's row. After that information has been retrieved, the method must also determine the type of the variable in which the cell belongs before creating the WHERE clause.
/*******************************************************
* The buildWhereClause method accepts a where list *
* which may or may not contain a previously defined *
* subset. *
* The method selects the cell selected by the popup *
* action and then builds a where clause which is *
* is inserted into the where_l list. *
*******************************************************/
buildWhereClause:
method where_l:update:list;
dcl object modelid = getnitemn(_self_, '_modelid_');
dcl list row_l = {}
col_l = {};
dcl num colnum value;
dcl char text type colname;
_self_._getPopupCell(row_l,col_l);
colnum = getitemn(col_l,1);
_self_._setActiveCell(row_l,col_l);
modelid._getDisplayedColumnName(colnum,colname);
where_clause = colname||'=';
modelid._getColumnAttribute(colname,'type',type);
if type = 'C' then do;
modelid._getColumnText(colname, text);
where_clause = strip(where_clause)||'"'||text||'"';
end;
else do;
modelid._getColumnValue(colname, value);
where_clause = strip(where_clause)||strip(put(value,8.));
end;
_self_._clearActiveCell();
if listlen(where_l) >= 1 then where_clause = 'and '||strip(where_clause);
rc = insertc(where_l,where_clause,-1);
row_l = dellist(row_l);
col_l = dellist(col_l);
endmethod;
|
The Complete Application
A complete application can be found on the Downloads tab in this sample.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
The complete sample can be viewed using the contents of this zip file.
After extracting the SAS transport file from the zip file, use PROC CIMPORT to recreate the catalog.
PROC CIMPORT LIBRARY=work INFILE='>path to sample37005.xpt<';RUN;
|
AF CATALOG=work.catalog.tablview.frame
|
| Type: | Sample |
| Date Modified: | 2009-11-12 11:16:52 |
| Date Created: | 2009-08-27 10:15:16 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | SAS/AF | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M0 | |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.1 TS1M0 | |||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.1 TS1M0 | |||
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M0 | |||
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M0 | |||
| Microsoft Windows 2000 Server | 9.1 TS1M0 | |||
| Microsoft Windows 2000 Professional | 9.1 TS1M0 | |||
| Microsoft Windows NT Workstation | 9.1 TS1M0 | |||
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M0 | |||
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M0 | |||
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M0 | |||
| Microsoft Windows XP Professional | 9.1 TS1M0 | |||
| 64-bit Enabled AIX | 9.1 TS1M0 | |||
| 64-bit Enabled HP-UX | 9.1 TS1M0 | |||
| 64-bit Enabled Solaris | 9.1 TS1M0 | |||
| HP-UX IPF | 9.1 TS1M0 | |||
| Linux | 9.1 TS1M0 | |||
| OpenVMS Alpha | 9.1 TS1M0 | |||
| Tru64 UNIX | 9.1 TS1M0 | |||